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':

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

`=VLOOKUP(4,$A$1:$B$4,2,TRUE)`

– Scott Craner Jun 9 '16 at 20:18