Index/Lookup two variables across multiple sheets

[andredl]

I have a list of unique entries and want to look up information in adjacent sheets that correspond to entries in that list using a formula.

Index and Match (my trusty friend) falls short here.

The example I have attached is for conceptual purposes, I can't combine my real dataset.

I hope someone can assist.

Thanks,
André

[humdingaling]

use indirect to get sheet

[:) Sixthsense :)]

In B2 Cell of Summary Sheet


=IFERROR(INDEX(INDIRECT("'"&$A2&"'!B:B"),MATCH(B$1,INDIRECT("'"&$A2&"'!A:A"),0)),"")


Drag it down and right...

[andredl]

Thanks a lot, both of you. It does exactly what I need, but could I get you to explain how it works though?

[:) Sixthsense :)]

Read the definitions of each functions Or make use of Evaluate button under the Formula's Tab (While using Evaluate the cursor should be in formula cell)

[humdingaling]

indirect is pretty easy to understand

look under Create a Reference To a Different Sheet
http://www.contextures.com/xlFunctions05.html