Forum Discussion
Solved
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+...
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")
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.
https://1drv.ms/x/c/804e6520232874cf/EblL2ks0DL5OvZ3FsWB7GNkBR_yblrNXFyVEdgdl_cJlWg?e=1pgGjt
Sure, here is the sample file.
