How to Lookup Data in a Column to the Left in Excel

The Excel Vlookup Function is probably the most popular lookup and reference function in Excel. It is used to look for a value in the leftmost column of a table of data, and return a value from a different specified column. For example, you may search for someone’s Employee ID in the leftmost column and return that persons start date from column 5.

However Vlookup has one nagging limitation. It can only search for a value in the leftmost column. So what do you do if you want to search for a value in column 3 and return a value from column 1. Well let’s find out.

The example below shows a list of salespersons and their sales figure for the month. Cell E3 contains the Max function, which is being used to return the maximum sales that month. The objective is to return the name of the salesperson who has achieved the maximum sales total.

Use the Index and Match Functions

The Index and Match functions in Excel can be used together to create a strong and dependable lookup formula. Most importantly they can be used to return a value to the left of the value you are looking for.

The Index function will be used to select the column which holds the value to return. In this case that is the column of names.

The Match function will look for the value in cell E3 in the column of sales totals and return the row number in which it was found. The row number is then handed to the Index function. The Index function will then return the name from that row.

The finished formula would look like this.

=INDEX(A2:A7,MATCH(E3,B2:B7,0))

The Index and Match functions are a great alternative to Vlookup when it doesn’t quite achieve what you need it to. Vlookup remains a wonderful function that is quick and easy to use. But there are other options worth exploring sometimes.