Excel INDEX MATCH to another file to dynamic location by header name without Table References

Question

I have 2 files. On the first file I have a table with ID column and other columns as well. in the "Messi" column I want to INDEX MATCH to get the value "5" for when ID = "AAA". notice that the second file is not a table and also that the columns in the second file are not in the same order as the first file.

any help please?

First File:

Second File:

Answer 1

In cell B5 of your First File:

=INDEX('[Second File.xlsx]Second File'!$C:$C, MATCH($A5, '[Second File.xlsx]Second File'!$A:$A,0))

Index/Match syntax for column lookup:

=INDEX([Result Column], MATCH([Lookup Cell], [Lookup Column], [Match Type]))

Using that I'm sure you'll be able to figure out which columns and cells you'll need to change to get the results for the other cells :)

EDIT: If you want to have the look up of the column headers then I suggest the below formula as it doesn't rely on INDIRECT which would only work when the second workbook is open:

=INDEX('[Second File.xlsx]Second File'!$A:$D, MATCH($A5, '[Second File.xlsx]Second File'!$A:$A,0), MATCH(B$4, '[Second File.xlsx]Second File'!$4:$4,0))

What's different:

• The first parameter in the INDEX function covers the entire table '[Second File.xlsx]Second File'!$A:$D
• There is a third parameter added in the INDEX function to look up the column header. MATCH(B$4, '[Second File.xlsx]Second File'!$4:$4,0)
  • B$4 looks up the column header
  • '[Second File.xlsx]Second File'!$4:$4 is where it looks at