Forum Discussion

Charlotte Mcnamee's avatar
Charlotte Mcnamee
Copper Contributor
Nov 08, 2018

Index Match Error

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,...
excel
Formulas and Functions
SergeiBaklan's avatar
SergeiBaklan
MVP
Nov 08, 2018

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?

 

  • Charlotte Mcnamee's avatar
    Charlotte Mcnamee
    Copper Contributor
    Nov 08, 2018

    Hello Sergei. 

     

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

    • SergeiBaklan's avatar
      SergeiBaklan
      MVP
      Nov 08, 2018

      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

       

  • Charlotte Mcnamee's avatar
    Charlotte Mcnamee
    Copper Contributor
    Nov 08, 2018

    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. 

Resources