excelindexingexcel-formulavlookupindex-match

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

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: enter image description here

Second File:

enter image description here

Solution

  • 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: