Two way lookup in excel with vlookup match

VLOOKUP function will search for the value in a table vertically and returns the value of the column based on the column index for matching row. The column index is defined in the formula and usually it is a constant value. With the combination of VLOOKUP MATCH function we can dynamically change the column index. This will enable two dimensional VLOOKUP in a table. This formula will function correctly even if you add or delete any column in the table.

vlookup match

How to use VLOOKUP MATCH

In this example you can see that the column index is replaced with MATCH function, MATCH function will check for matching column heading and return the column number. The columns defined in VLOOKUP table and the range in Match function should be same to give correct result in this formula.

two way lookup
=VLOOKUP($F7,$A:$D,MATCH(G6,$A$2:$D$2,0),0)

In this formula, VLOOKUP will search for 8 in column A and MATCH  function will check for Employee Name in row 2. Since Employee Name is matching in Column B MATCH function will return value 2 to column index.

When you insert a column in the table, you can see that the hard coded column index formula will not give the desired result. However, the VLOOKUP with MATCH will not be impacted.

In VLOOKUP MATCH formula we can change both lookup value and column index without changing formula.

Download Excel wookbook with formula.

Leave a Comment