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
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
cntvertex in Discussions on
13 Replies
Tabs and Dark Mode
cjc2112 in Discussions on
22 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies
description for autoplay blocking in settings page
HotCakeX in Discussions on
8 Replies