=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)))
=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)))
attached is the sheet names list. Please note, sheet are in a different file.
i put the formula in P2.attached is the sheet names list. Please note, sheet are in a different file.
thanks for the reply.Try...
Excel Formula:=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
Excel Formula:=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!
[Stewardship.xlsm]Mechanical
, etc.yes, it is working now! yes, it only works while workbooks are open. Thanks so much !!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!