excelexcel-indirect

why does my index match formula with an nested indirect reference return as a #REF... perrrrplexed

here is the formula:

INDEX(INDIRECT(M$8&"!BS:BS"),MATCH($B11,INDIRECT(M$8&"!B:B"),0))

the output for M$8 is week 1_mon

the formula within M$8 is "week 1_"&M10

the output for M10 = mon

I checked that the text for the name of sheet is the same as the output for M$8. The result was "TRUE".

I am perplexed!!

Solution

  • You need quotes round the sheet name because it has a space in it, so:

    =INDEX(INDIRECT("'"&M$8&"'!BS:BS"),MATCH($B11,INDIRECT("'"&M$8&"'!B:B"),0))