VLOOKUP To The Left With INDEX MATCH

Anyone who has used Excel for a while uses VLOOKUP.


In the below example I use VLOOKUP to pull back the location of an employee based on the user Id.

However VLOOKUP cannot look to the left. What if I knew the State but did not know the employee?                   

If I wanted to use VLOOKUP, I would need to rearrange the columns putting the Employee Name in Column D.                                

That’s because you cannot use negative numbers in a VLOOKUP formula to return columns to the left).                               

                               

To get around the limitation you can use a combination of INDEX and MATCH to lookup up data to the left.                         

                               

=INDEX($A$2:$C$6, MATCH(E2,$C$2:$C$6, FALSE),2)

I placed the above formula in cellF2 and search on the Location, can return the employee name.                              

                               

So what is this formula doing??? Let’s break it down.                     

                               

In English, INDEX goes to the data range and returns you the value in the intersection of the row number and the column number.                             

                               

                               

                               

                               

                               

I can replace the row lookup in the above formula with the MATCH function.                     

                               

The MATCH function will tell the Index function which row to look at.                    

Four our purpose, MATCH tells Excel to search the data range and find the relative row number where you find a match.                               

                               

=MATCH(E3,$C$2:$C$6, FALSE) will return the number 3 since Iowa is the 3rd row in the array (column  C) data                 

                               

Putting the two functions together gives you the super user ability to look to the left in Excel.                   

                               

So how do I select the column of data to return?                             

                               

Remember our range is three columns, A B and C.  If you want to return the data in Column A, select 1, Column B use 2 and column C put 3.                

                               

If I wanted to return the Id number I can put a 1 at the end of the formula                          

                               

=INDEX($A$2:$C$6, MATCH(E2,$C$2:$C$6, FALSE),1)                      

                               

In fact you can use the INDEX MATCH combination to completely replace VLOOKUP as it will allow you to look left or right of you lookup value.    


Now that you know the secret, how will you use INDEX MATCH?