Nested Index/Match with indirect function lookup

[aviniarsky]

Hi All,

I'm having issues with getting my indirect function to work when embedded into my index match formula. My attempts result in a "#value!" error.

Initially, i tried indirect with vlookup which worked successfully, and now i'm trying to make it work with index-match.

Here's a snapshot:

with my formula being -=INDEX(INDIRECT("'"&C4&"'!B1:B3"),MATCH(A5,"'"&C4&"'!A1:A3",0))

I have a second sheet - called Jul, which looks like this:


What's the reason for my error?

Thanks in advance.

 

[NARAYANK991]

Hi ,

The formula you have posted is :

=INDEX(INDIRECT("'"&C4&"'!B1:B3"),MATCH(A5,"'"&C4&"'!A1:A3",0))

Surely , the MATCH function too will have to use the INDIRECT function ?

Try this :

=INDEX(INDIRECT("'"&C4&"'!B1:B3"),MATCH(A5,INDIRECT("'"&C4&"'!A1:A3"),0))

Narayan