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 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):
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
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.