Index Match

[ekhawaja]

Hello,

How can I modify the formula, I would like to do a search across sheet 2, sheet 3 and sheet 4 for a value in sheet 1?

=INDEX([sheet2!$W:$W,MATCH($sheet1!AJ5,[sheet2!$E:$E,0)

Thanks in advance!

 

[Domenic]

Try...

=LET(SheetList,{"Sheet2","Sheet3","Sheet4"},LookupSheet,INDEX(SheetList,MATCH(TRUE,COUNTIF(INDIRECT("'"&SheetList&"'!E:E"),AJ5)>0,0)),INDEX(INDIRECT("'"&LookupSheet&"'!W:W"),MATCH(AJ5,INDIRECT("'"&LookupSheet&"'!E:E"),0)))

or

=LET(SheetList,$AM$5:$AM$7,LookupSheet,INDEX(SheetList,MATCH(TRUE,COUNTIF(INDIRECT("'"&SheetList&"'!E:E"),AJ5)>0,0)),INDEX(INDIRECT("'"&LookupSheet&"'!W:W"),MATCH(AJ5,INDIRECT("'"&LookupSheet&"'!E:E"),0)))

...where AM5:AM7 contains the list of sheet names.

Hope this helps!

 

[ekhawaja]

 

[ekhawaja]

attached is the sheet names list. Please note, sheet are in a different file.

 

[ekhawaja]

attached is the sheet names list. Please note, sheet are in a different file.

attached is the sheet names list. Please note, sheet are in a different file.

i put the formula in P2.

 

[ekhawaja]

Try...

=LET(SheetList,{"Sheet2","Sheet3","Sheet4"},LookupSheet,INDEX(SheetList,MATCH(TRUE,COUNTIF(INDIRECT("'"&SheetList&"'!E:E"),AJ5)>0,0)),INDEX(INDIRECT("'"&LookupSheet&"'!W:W"),MATCH(AJ5,INDIRECT("'"&LookupSheet&"'!E:E"),0)))

or

=LET(SheetList,$AM$5:$AM$7,LookupSheet,INDEX(SheetList,MATCH(TRUE,COUNTIF(INDIRECT("'"&SheetList&"'!E:E"),AJ5)>0,0)),INDEX(INDIRECT("'"&LookupSheet&"'!W:W"),MATCH(AJ5,INDIRECT("'"&LookupSheet&"'!E:E"),0)))

...where AM5:AM7 contains the list of sheet names.

Hope this helps!

thanks for the reply.

 

[Domenic]

First, when listing your sheet names, don't include the exclamation ( ! ) at the end of the name. So it should be [Stewardship.xlsm]Mechanical, etc.

Secondly, the lookup workbook must be open.

Hope this helps!

 

[ekhawaja]

First, when listing your sheet names, don't include the exclamation ( ! ) at the end of the name. So it should be [Stewardship.xlsm]Mechanical, etc.

Secondly, the lookup workbook must be open.

Hope this helps!

yes, it is working now! yes, it only works while workbooks are open. Thanks so much !!

 

[Domenic]

You're very welcome, cheers!