Forum Discussion
I'm having trouble with INDEX and MATCH with INDIRECT inside them.
In the sheet JAN - OVERALL TABLE, I'm trying to extract the numerical IDs corresponding to each text ID from the sheets mentioned in the G3-G10 cells.
I've tried both the VLOOKUP and INDEX+MATCH functions to extract the "Number IDs" in B3 of JAN OVERALL TABLE but I am only observing #VALUE errors in both cases. Any help would be appreciated! :)
(I couldn't upload the file cuz I'm new here)
Here is one solution using XLOOKUP() and VSTACK().
=XLOOKUP(Table1428[@ID],VSTACK('BRB a:ATA h'!$H$2:$H$1000),VSTACK('BRB a:ATA h'!$K$2:$K$1000),"Not Found")
5 Replies
INDIRECT() can't handle multiple reference at a time. So, =INDIRECT("'"&G3:G10&"'!H2:H50") will produce error. INDIRECT() will work on single reference like =INDIRECT("'"&G3&"'!H2:H50"). To work on multiple reference you will need few iterative formula like REDUCE() and VSTACK() and then use XLOOKUP() or FILTER() function. You may share a sample file link via OneDrive or Google-Drive.
