0

I have 2 sheets. One is for the child table and the other is for the master table. I need to get the master_id from the master table which is in the master table sheet.

As you can see below, with the formula i get from this Excel: Check if Cell value exists in Column, and then get the value of the NEXT Cell, it didn't work for me.

Thank you.

Child Table

    A              B                C
child_id      reference_no      master_id
1             2017001           =IF(MATCH(B2,master.B2:B15,1), "NO MATCH", VLOOKUP(B2,master.A2:B15,2,0))
2             2017002           NO MATCH

Master Table

    A             B
master_id     reference_no
a1            2017002
a2            2017003

2 Answers2

1

The problem with what you are trying to do is that VLOOKUP searches for a key in the first column, and then gives the option of returning either the value in that column or the column immediately to the right. But your Master table has it backwards, with the key in column B and the value you want in column A.

I might recommend that your refactor your master spreadsheet as follows:

Master Table

A             B
reference_no  master_id
2017002       a1
2017003       a2

Then you can enter the following formula into column C of your child spreadsheet:

=IFERROR(VLOOKUP(B2,master.A2:B10,2,FALSE),"Not found")

If you cannot/don't want to change your master spreadsheet, there are workarounds, but the formulas are more complex than what I have above, q.v. here:

https://superuser.com/questions/645039/excel-vlookup-by-second-column-using-table-name-as-range

Tim Biegeleisen
  • 502,043
  • 27
  • 286
  • 360
1

INDEX returns the particular row in a table.

MATCH returns the position of a value in a table.

You want to use them together:

IndexMatch

user1274820
  • 7,786
  • 3
  • 37
  • 74