0

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

Kobe2424
  • 147
  • 7
  • What is exactly the issue? You can use for that `INDEX/XMATCH` for example. Share in the question the formula you are trying to use and the error or the wrong result you are getting. Thanks – David Leal Feb 20 '23 at 19:58

2 Answers2

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
Kairu
  • 381
  • 1
  • 9
  • That was the issue that I didn't know that the column i was searching for was column C. anyway I took it a couple of steps further and I found the answer I was looking for: =@INDIRECT("'[Second File.xlsx]Second File'!" & ADDRESS(MATCH([@ID],'[Second File.xlsx]Second File'!$A:$A,0),MATCH("Lionel Messi",'[Second File.xlsx]Second File'!$1:$1,0))) I can even find the Header rows in both sheets if I tried to. But this was the answer I was looking for. I hope it can help someone else too. – Kobe2424 Feb 21 '23 at 22:54
  • I added a new formula which doesn't use `INDIRECT` which only works when you have the second workbook open – Kairu Feb 22 '23 at 00:16
  • Kairu how would you keep the format of that good formula you've added but add another MATCH - let's say I don't know in which column to look for $A5. – Kobe2424 Apr 06 '23 at 09:56
0

I took it a couple of steps further and I found the answer I was looking for:

=@INDIRECT("'[Second File.xlsx]Second File'!" & ADDRESS(MATCH([@ID],'[Second File.xlsx]Second File'!$A:$A,0),MATCH("Lionel Messi",'[Second File.xlsx]Second File'!$1:$1,0)))

I can even find the Header rows in both sheets if I tried to. But this was the answer I was looking for. I hope it can help someone else too.

Kobe2424
  • 147
  • 7