The Better vLookUp

Spreadsheet mastery comes in varying degrees, but there is one formula that seems to separate basic users from more advanced users. That formula is vLookUp.

To the advanced users, vLookUp is child’s play. Even the intermediate user thinks vLookUp is a basic tool to be used without much thought. However, apprentice users are often intimidated and confused by this formula.

If vLookUp challenges your Excel abilities, you might consider reading (or watching) a few vLookUp tutorials first. Then come back to this post with the confidence of an Excel Guru.

   THE PROBLEM WITH vLOOKUP

If your dataset is arranged with the lookup value on the left and sorted correctly with your lookup range all in one table, vLookUp might work fine. That is… until a column gets added, deleted, or moved. Now the return value column number has changed. There are also some limitations if you are trying to use the approximate match feature (but I rarely use that anyway).

If you use vLookUp frequently (or its sister formulas lookup and hlookup), but are frustrated by its limitations, you should consider ditching this underpowered tool for the new and improved Binford 6100 IndexMatch formula.

   BINFORD 6100 INDEXMATCH FORMULA

IndexMatch might be slightly more complicated, but it’s significantly more useful. Now I find myself using IndexMatch even when vLookUp would work fine. But I like my formulas like I like my trucks and chainsaws: powerful.

IndexMatch­, as you guessed, is two formulas, one embedded in the other. To combine these formulas to search like you would with vLookUp is quite easy. Once you get the hang of it, there are plenty of additional ways to use IndexMatch, but here are the basics.

The Index formula has three arguments, but you only need the first one, the array. This is the column containing the result you want to return. It doesn’t need to be the left column, it can be anywhere in any dataset.

The next argument is the row number, which gets replaced with the Match formula. The Match formula has three arguments. The first is the lookup value, which is the information you already know about what you are searching for, like the customer’s name, the date, or the order number. It could be a number, text, or (most likely) a cell reference.

The next argument is the lookup array, which is the column containing your lookup value.

And the last argument is the Match Type. Use -1 for greater than searches, 0 for exact searches, and 1 for less than searches. Or, you can leave it out altogether and it will default to an exact match search.

Look at the following screenshot of a list of loans. If I know the payment amount (in cell L4) but need to find the date, I can use IndexMatch (in cell L5).

The next screenshot illustrates another common use of IndexMatch. I have a list of loans in cells K4:K7 in one file, but I need to know the loan terms from another file. I can use the IndexMatch in L4 and drag it down to L7, giving me the correct loan term for each corresponding loan number.

So next time you find yourself needing to use a lookup function in your spreadsheet, consider using the tool the pros use, the IndexMatch. Not only will it work better, but it is sure to impress as well.