Problem with INDEX, INDIRECT, MATCH, MATCH

**JO505** · 08-31-2011, 04:56 PM

I have been unable to get the Indirect portion of a formula to work.

The following formula works but when I tried to include an Indirect Index all I get is #REF!

Please Login or Register  to view this content.

This is how I tried to add the Indirect portion.

Please Login or Register  to view this content.

Thanks

Jim O

**bentleybob** · 08-31-2011, 06:27 PM

Can you attach a file? Hard to debug a formula with no context.

**JO505** · 08-31-2011, 08:22 PM

I will send a file as soon as I can put together a sample. The actual file is a little involved so I will strip out the unnecessary data.

As an aside the first formula works but I want to use Indirect inplace of the '2011'! value so I can easily refference different years (sheet tabs) from a dropdown list. The $L$3 cell will contain the dropdown list, all other refferences would remain the same.

Jim O

**teylyn** · 08-31-2011, 09:01 PM

Hello,

the Indirect() statement is not formed properly.

INDIRECT($L$3&"!"&DJ$9:DJ$24)

This will take the content of cells DJ9 to DJ24 and will try to interpret that as a range. I think you really want

INDIRECT($L$3&"!DJ$9:DJ$24")

Try

=INDEX(INDIRECT($L$3&"!DJ$9:DJ$24"), MATCH($O$3,INDIRECT($L$3&"!$A$9:$A$24"),0), MATCH($M18,INDIRECT($L$3&"!DJ$3"),0))