Using index match & indirect together

[deduwa]

Hi,

I have a table in the master sheet like below where the ?? denotes where I want to add a formula.
I also have multiple other reference worksheets, named in the format Jul_15, Aug_15 etc etc.
From each reference worksheet, I want to pull into the master sheet the same data from column Z.
In all the reference worksheet, the names all appear in column A and the data I want to pull in is always in column Z. Can this be done using index match indirect? (Do not want to use a macro for this)

Thanks

	Jul_15	Aug_15	Sep_25	Oct_15
Adam	??
Barry
Cat
Del
Elon

 

[Fluff]

How about
=index(indirect("'"&B$1&"'!$Z$1:$Z$100),match(a2,indirect("'"&B$1&"'!$A$1:$A$100"),0))

 

[deduwa]

Thanks Fluff,

I get a 'error with this formula' pop up and it highlights the below area....

 

[Fluff]

Oops, missed a closing " in the first indirect

=index(indirect("'"&B$1&"'!$Z$1:$Z$100"),match(a2,indirect("'"&B$1&"'!$A$1:$A$100"),0))

 

[deduwa]

Great - that works!!

Thanks

 

[Fluff]

You're welcome & thanks for the feedback.