Excel VLOOKUP returns #N/A when allowing approximate match, but returns value when requiring exact match?

1
votes

Run across a very strange issue that I am curious about. In Excel, I was doing a VLOOKUP to match contact names with a unique ID found in another database. Formula for example: =VLOOKUP(B2,Sheet2!A:B,2,TRUE), very basic. I used approx. match but, of course, some instances still returned #N/A. However, I did double checks with a quick control-F and found a number of instances returning NA when there was in fact a (exact) match. I was troubleshooting and trying a number of things but nothing was working. Many checks confirmed the cells were equal, there was nothing tricky like hidden characters, very peculiar. Anyway, on a whim I changed from approximate match to exact match and lo and behold it worked! The VLOOKUP was returning the correct value.

I'm trying to understand why this is. Obviously if you were requiring an exact match but there was only an approx. match it would return NA. But it doesn't make sense the other way around. An exact match should be returned when allowing even approx. matches! So just trying to understand what is going on behind the scene in VLOOKUP/Excel to make this phenomenon occur.

Appreciate any insight!

1
excelexcel-formulavlookup
The lookup range must be sorted in ascending order when using TRUE as the forth criterion, otherwise you run the chance of errors. – Scott Craner

1 Answers

1
votes

When working with approximate VLOOKUP, the key-column (always the 1st column of matrix-range) in the lookup-matrix should always be sorted in ascending order. The reasons for this is the following:

Imagine a key column with the values [2,3,10,4] in the given order. When the key '4' should be looked-up approximately, the function will start comparing the available keys in the column, beginning with '2'. After '3' was passed, '10' follows, which is already greater than the searched '4'. Consequently, the function stops searching and returns the target value of the previous key-row ('3') as an "approximation".

Now, given another example [10,2,3,4]. Here, approx. VLOOKUP will stop searching right after the first key comparison as '10' is greater than the searched '4'. Since no previous key-row is available, the returned value will be #N/A. However, an exact VLOOKUP would have found the matching key as it does not stop searching.

Besides key order, it is recommended to have all the key-values in the same format.

Hope this helps.