Indexing multiple worksheets using INDEX MATCH and I think INDIRECT

[rorhall]

Hi,

I am wanting to index multiple worksheets, match to data in a cell and return the required information.

The following formula works for a static name of the spreadsheet I am indexing, 'Gillig'
=IF(ISNA(INDEX(Gillig!B:B,MATCH(SCHEMWS!H12,Gillig!C:C,0))),"",INDEX(Gillig!B:B,MATCH(SCHEMWS!H12,Gillig!C:C,0)))

What I want, is to be able to enter the name of another worksheet, in a cell, and have the formula return the information. I am struggling with this attempt
=IF(ISNA(INDEX(INDIRECT("H4"),MATCH(SCHEMWS!H12,INDIRECT("H4")*C:C,0))),"",INDEX(INDIRECT("H4"),MATCH(SCHEMWS!H12,INDIRECT("H4")*C:C,0)))

H4 contains the worksheet name. Any ideas and or, do I need to provide more information.

Thanks

 

[Eric W]

Welcome to the MrExcel Board!

Try:

=IFERROR(INDEX(INDIRECT("'"&H4&"'!B:B"),MATCH(SCHEMWS!H12,INDIRECT("'"&H4&"'!C:C"),0)),"")

 

[rorhall]

Welcome to the MrExcel Board!

Try:

=IFERROR(INDEX(INDIRECT("'"&H4&"'!B:B"),MATCH(SCHEMWS!H12,INDIRECT("'"&H4&"'!C:C"),0)),"")

Works great. Thanks for your help and time.

Cheers,
Rory

 

[Eric W]

You're welcome.