Search code examples
excelexcel-formulamatchvlookup

How to match a row with Excel column and VLOOKUP the value?

I have two sheets with two tables that look like this:

Table 1:

Index category
1234 Cars
5678 Trains
9101 Motorcycles
177 Motorcycles

Table 2

Index Cars Trains Motorcycles
1234 100 150 15
5678 - 200 167
344 355 455 156

I want to match the category rows from table 1 with the assigned value that is available in table 2 by matching the name.

I have tried:

vlookup('Table1'!A2,"Table2"!A:D,Match(B2,'Table2'A1:D1))

but this doesn't work. So the table should look like this:

Index category Values
1234 Cars 100
5678 Trains 200
9101 Motorcycles -
177 Motorcycles -
Solution

  • Why not use Index and Match instead, Would continue to work even if you made changes to the tables,

    =IFERROR(INDEX(Table2,MATCH(A2,Table2[Index],0),MATCH(B2,Table2[#Headers],0)),"")

    That would give you your result. A2 and B2 are the cells where your new table data starts, and you can change that to suit.