How to Lookup a Value across multiple tables (approx 50 tables)

[thewiseguy]

Hi all. This is a function that I will need to use over and over again, so I'd like to take the best approach.

What function would you recommend using in order to find a specific value across 50 tables? Think of an XLOOKUP but x50.

Can anyone point me in the direction of what I am looking for?

Many thanks in advance!

 

[Domenic]

Where are your tables located?

 

[thewiseguy]

each table has its own separate tab.

 

[Domenic]

Try adopting the following formula...

=LET(SheetList,G2:G50,TargetSheet,INDEX(SheetList,MATCH(TRUE,COUNTIF(INDIRECT("'"&SheetList&"'!A2:A100"),H2)>0,0)),XLOOKUP(H2,INDIRECT("'"&TargetSheet&"'!A2:A100"),INDIRECT("'"&TargetSheet&"'!B2:B100"),,0,1))

...where G2:G50 contains your list of sheet names, H2 contains the lookup value, A2:A100 contains the lookup range, and B2:B100 contains the return range. Note, however, depending on how you've named your 50 sheets, it may be possible to avoid having to list your sheet names in a range of cells, as we've done in this example. How have you named your sheets?

Hope this helps!