whats wrong with this formula

[kastsnow]

=IF(MATCH(F7,Calendar!L1:R1,0),OFFSET(Calendar!L1,,,Calendar!L2:L25,),"NFG")

 

[Jonmo1]

In A2 (or wherever you want)

=HLOOKUP($F$7,Calendar!$L$1:$R$25,Rows(L$1:L2),FALSE)

And copied/filled down as far as you like.

 

[T. Valko]

A2 would be fine!

I want the 15 cells of info slated below day 2 brought to sheet 1

TYVM

Try this...

Sheet2 A1:F1 = column headers Day1, Day2, Day3, etc.

On Sheet1 select a 15 cell array starting from cell A2. So, select the range A2:A16.

Enter this array formula**:

=INDEX(Sheet2!A2:F16,0,MATCH(F7,Sheet2!A1:F1,0))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.