I have an excel document with 20+ different tables. I would like my Excel formulas in my working sheet to find each table and then extract the data.
=INDEX('[Inputs.xlsx]Table 1'!$A$1:$BV$10000, row_num, column_num)
I have got row number and column number sorted but I would like to put a formula for the table array so that a specific table is found and data is extracted.
Instead of the table array starting cell of A1 I would like to add a MATCH formula to find the specific row that the table header I want to match is in and start by array there.
=MATCH(*$C1*, '[Inputs.xlsx]Table 1'!$A1:$A10000,0)
C1 is text and the table headers in the Input file contains some of the text in C1 hence a partial match.
So I would like to place the match formula within the index formula but I have not had any luck. Any tips? Thanks!
INDEX('[Inputs.xlsx]Table 1'!$A$&MATCH(*$C1*, '[Inputs.xlsx]Table 1'!$A1:$A10000,0):$BV$10000, row_num, column_num)