Exceljet

Quick, clean, and to the point

Danger: beware VLOOKUP defaults

by Dave Bruns | September 2, 2015

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.

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 if not, because VLOOKUP might give you a totally incorrect result. Even worse, it 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.

VLOOKUP 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

VLOOKUP wrong match example 2

In the second example, VLOOKUP again is defaulting to an approximate match, since no 4th argument is supplied. VLOOKUP required 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.

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

Takeaway

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

I really appreciate your delivery of the videos, the clean and sharp documentation and pleasant to the eye website, uncluttered, direct, short and snappy. From what I've checked out elsewhere nothing compares... - Christina
Excel foundational video course
Excel Pivot Table video training course
Excel formulas and functions video training course
Excel Shortcuts Video Course
Excel Charts video training course