Lookup selected quarter-end dates

Question

On Sheet1 I have a table across several columns with dates (quarters, say: 03/31/2014, 06/30/2014 and so on) in the first cell of each column.

On Sheet2 I have another table with four columns where I want to fill each first cell with the quarterly dates from the table in Sheet1. However the dates to be chosen depend on another cell (containing a date value).

For instance, if we are in May (reference cell) I want to populate the first cell in Column1 with the last quarter before May (ie 03/31/2014) and the other three first cells with the subsequent quarter-end dates, so the second column would lookup 06/30/2014 from Sheet1, and so on.

Answer 1

An alternative to looking up the dates might be to create them in the second sheet. If the columns there were A:D then, in A1:

=CHOOSE(MOD(MONTH(G1),3)+1,EOMONTH(G1,-3),EOMONTH(G1,-1),EOMONTH(G1,-2))  

and in B1 copied across to D1:

=EOMONTH(A1,3)  

where G1 is assumed to be the reference cell.