A Better VLOOKUP

Image of a spreadsheet print out and calculator.

I know what you are thinking. I was thinking the same thing before I started using the combination of the Index and Match functions. 

Vlookup is a function that allows you to find a value in the left most column of a cell range and then return a value from a cell a certain number of columns to the right of the matched value. In Example 1, the Vlookup function is looking for the value found in A10 (“Pear”) within cells A2-A5 (Fruit Column). If found, it will return the value 4 columns to the right. Counting starts with the leftmost column, column A in this case. The “False” statement returns an exact match instead of an approximate match if you use “True” or leave the fourth item blank. 

Screen shot of spreadsheet Example 1

The main shortcomings of Vlookup that I have are:

1) The data must be in the leftmost column of your array.

2) Vlookup be slow and takes a lot of memory.

3) You cannot add columns in the specified range without adjusting the function.

4) If there are duplicates in the first column it will only return the first match.

The Match function returns the position of a value in a specified one-column or one-row range. In Example 2, Match returns the value of 3 for “Pear” since it is in the 3rd position in the range of A2-A5. The Index function pulls a value based on the row/column position within a specific cell range. In Example 2, the range is D2-D5 and the value (“Green”) is in 3rd row (from Match function) and the 1stcolumn of the Index specified range. 

The Index Match combo provides a solution to three of the four shortcomings of Vlookup. 

1) Your lookup data no longer must be in the left most column of the array. The Match function could be referencing column F rather than A. 

2) Index Match uses significantly less memory because you only bring in the columns that you need.

3) Since you are only selecting the columns that you need, you can add columns to your data without restrictions.

Screen shot of spreadsheet Example 2

While Index Match does not fix the duplicate issue, Index Match will bring the last instance of the item that you are trying to match. This could be a could control test with Vlookup that you do not have any duplicates in your data.

I hope that you give the Index Match combo a try. I am sure you will like it once you do. 

If you would like to learn more or have BTG assist with an upcoming project, please contact us at info@bluetridentgroup.com or book a free consultation here.