Problem using Match with Indirect

Question

Hi,

I'm stumped using    MATCH(A$2,INDIRECT("'Data Fall 2015'!C"&AA3):C$108,0)

The match function returns a #VALUE result, even though match returns the proper number when I don't use the INDIRECT portion of it. I'm including side-by-side picture that shows that the two formulas end up at the same place. On the left you see when I don't use INDIRECT (that is, I hard-coded $C$56), and that one correctly returns the value. The column on the left shows subsequent steps of the formula evaluation when I do use INDIRECT. At the bottom you see they're both at the same place, but this one returns #VALUE instead of 56.

 

The only difference I see is that the text is in italics in different spots. I don't know what those italics mean for Excel.

Can someone help me, please?

Alex

Answer 1

Put the whole range inside the INDIRECT

=MATCH(A$2,INDIRECT("'Data Fall 2015'!C"&AA3&":C$108"),0)

Answer 2

Thanks! You got it! Saved me a lot of headache :)

Answer 3

I have to go to a meeting now, but I'll try this as soon as I get back. THANKS!!!!!