Index Match Error

Charlotte Mcnamee
New Contributor

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) 

4 Replies

Hi Charlotte,


You may use INDIRECT

=INDEX(INDIRECT("'[Inputs.xlsx]Table 1'!$A$" & MATCH(...)), ..

but it works if only your file is opened.

Why don't you use Excel tables with structured references?


Thanks. I have not used structured references because I have about 100 input files (which change every year) and I want to avoid changing each one. 

Hello Sergei. 


Now it says I have too many arguments for this function. Any work arounds for this? Thanks!

I imitated on my file, formula looks like

=INDEX(INDIRECT("'[Book1]Sheet1 plus'!$F$" & MATCH(C1,B1:B7,0) & ":$F$8"),3,0)

your one shall have the same structure


Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
30 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
9 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
29 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies