Summary

Many users aren't aware of it, but VLOOKUP will use approximate match mode by default. This can be a disaster because VLOOKUP can return a totally incorrect result. Read below to learn how match modes work in VLOOKUP, and how to avoid this dangerous problem.

By default, VLOOKUP will do an approximate match. This is a dangerous default because VLOOKUP may quietly return an incorrect result when it doesn't find your lookup value. Read below to see some examples of how VLOOKUP can cause trouble when you don't manage match behavior.

Note: the MATCH function has this same behavior – match type is optional and defaults to approximate match.

When VLOOKUP is in approximate match mode, it assumes your table is sorted in ascending order, and does a binary search. As a result, when VLOOKUP finds a value that's greater than the lookup value, it will fall back, and match a previous value. In other words, it returns the last number that is less than or equal to the lookup value.

This is all fine and dandy when your data is sorted nicely, but it can be a disaster with unsorted data, because VLOOKUP might give you a totally incorrect result. Even worse, the result might look completely normal.

Video: Great video by Oz du Soleil on how binary search really works in Excel.

To illustrate, here are two examples below, both of which show incorrect results with VLOOKUP in approximate match mode.

Wrong match - example #1

In this example, there is no invoice 100235, but because VLOOKUP defaults to approximate match, it finds a result anyway.

VLOOKUP approximate match wrong result 1 - missing value

Wrong match - example #2

In the second example, VLOOKUP again is defaulting to an approximate match, since no 4th argument is supplied. VLOOKUP requires the table to be sorted when doing an approximate match, otherwise, results are unpredictable. In this case, the table isn't sorted and we simply get the wrong result (but note that there is no error):

VLOOKUP approximate match wrong result 2  - not sorted

The fix

Both problems above can be fixed by forcing VLOOKUP to do an exact match. Just supply the 4th argument (range_lookup) as FALSE or 0. In exact match mode, VLOOKUP will return the correct result if the lookup value is found and #N/A if not.

=VLOOKUP(value,table,column) // danger, approximate match
=VLOOKUP(value,table,column,0) // exact match

Takeaway

Leaving VLOOKUP in its default mode can be dangerous. To avoid this problem, I recommend you always set the match mode explicitly as a reminder of what you expect. Also, when you do want to use approximate matching, be sure your table is sorted.

Dave Bruns Profile Picture

AuthorMicrosoft Most Valuable Professional Award

Dave Bruns

Hi - I'm Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.