1

I have not been able to find Microsoft reference that explains the true behavior of VLOOKUP with the range_lookup setting set to 'TRUE' (which means finding an "approximate match"). Their reference only instructs you to sort the lookup table in ascending order to attain "correct results" but does not explain how the algorithm works so that one could predict its behavior in unsorted tables.

Many articles online attempt to explain why a sorted lookup table is important by proposing the following behavior when range_lookup is set to 'TRUE':

  1. VLOOKUP goes down the list on the leftmost column until it finds a lookup value that is greater than the one being looked up then stops looking.
  2. VLOOKUP returns the output value associated with the lookup value found in the row ABOVE the one it stopped looking at.

This would certainly explain why an unsorted list would lead to problematic outputs. But still, I decided to test it to see if that was indeed the way this functioned. Consider the following lookuptable with its top-left cell in A1:

1   a
99  b
3   c
4   d

Now let's do some value lookups:

Search for 1: Consider VLOOKUP(1,A1:B4,2,TRUE) in the context of the proposed algorithm. VLOOKUP goes down the left column, sees a 1, keeps going, sees a 99. That's greater than 1. It returns the value in the second column of the row above it. Fine. Algorithm expects a return of 'a'. Excel returns 'a'.

Search for 2: With VLOOKUP(2,A1,B4,2,TRUE), VLOOKUP goes down the left column, sees a 1, keeps going, sees a 99. That's greater than 2. It returns the value in the second column of the row above it. Works according to the algorithm. Algorithm expects a return of 'a'. Excel returns 'a'.

Search for 3: With VLOOKUP(3,A1,B4,2,TRUE), VLOOKUP goes down the left column, sees a 1, keeps going, sees a 99. That's greater than 3. It returns the value in the second column of the row above it. Works according to the algorithm. Algorithm expects a return of 'a'. Excel returns 'a'.

Search for 4: With VLOOKUP(3,A1,B4,2,TRUE), VLOOKUP goes down the left column, sees a 1, keeps going, sees a 99. That's greater than 4. It should return the value in the second column of the row above it. But it doesn't. Algorithm expects a return of 'a' but Excel returns 'd'!

Does anyone know the actual algorithm?

edit: I made a mistake but my questions still stands. See my comment below.

  • I get 'a' not 'd' with =VLOOKUP(4,$A$1:$B$4,2,TRUE) – Scott Craner Jun 9 '16 at 20:18
  • Now if you go greater than 99 it will return 'd', because as it goes down it tries to find where 100 is greater than one cell but less than the next. It does not find it so it returns the last cell in the range that has a number. – Scott Craner Jun 9 '16 at 20:20
  • Sorry I made a mistake. The range I used was one row bigger than the range of the lookup table. And that means it included an empty row. So it was 'A1:B5'. But I did get a lookup value of 4 returning d in that instance. Any idea why that is? That still doesn't mesh with the proposed algorithm since it should have stopped at '99'. – user2686410 Jun 9 '16 at 20:25
  • 1
    In case of exact match you can write False instead of True and no need to ascending order Tutorial – user555689 Jun 9 '16 at 22:24

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Browse other questions tagged or ask your own question.