Curious about the logic for VLOOKUP w/"true"

[Monsignor]

I was messing around with VLOOKUP and wanted to see the result of using true instead of false with an unordered list.

Is there any rhyme or reason as to why we get the results we get in the image below?

I know the right way to set this up. However, the question is in understanding what Excel did to generate:

• The VLOOKUP skips Marc and brings back Bill's age
• Kay isn't in the lookup range but we get Bill's age
• Both Carl and Carla bomb out as errors even though they're in the lookup range
• Freddie's age is right
• Ron's age is right
• Todd ends up with Carl's age

Usually when the unexpected answer comes back we can trace Excel's logic. Here, I can't see the logic. Does Excel just freak out and return random information?

 

[Gerald Higgins]

The Excel help just says that if the first column of "Table array" (E3:E16 in this case) is not sorted, Excel might not return the correct value, without shedding much light on why it returns the values it does.
If we knew for certain that there was some kind of logical rule behind this, then it might be useful in some circumstances.

I think it's interesting that of the 14 possible values that could have been returned, only 4, plus #N/A, actually do get returned....
29 - the final value in the table
31
44 - the value associated with the first name (alphabetically) in the table (if you replace Bill with Zebedee in col E, you don't get 44 returned any more).
72 - the highest value in the table.

It's interesting to play around with different possible names in the table - try substituting "Bill" with various other names.

It would probably be possible to work out the logic using a brute force / trial and error approach.

 

[Aladin Akyurek]

The Excel help just says that if the first column of "Table array" (E3:E16 in this case) is not sorted, Excel might not return the correct value, without shedding much light on why it returns the values it does.

A description that is not that bad. I guess trying to explain binary search (see below) instead would be too technical...

If we knew for certain that there was some kind of logical rule behind this, then it might be useful in some circumstances.

I think it's interesting that of the 14 possible values that could have been returned, only 4, plus #N/A, actually do get returned....
29 - the final value in the table
31
44 - the value associated with the first name (alphabetically) in the table (if you replace Bill with Zebedee in col E, you don't get 44 returned any more).
72 - the highest value in the table.

It's interesting to play around with different possible names in the table - try substituting "Bill" with various other names.

It would probably be possible to work out the logic using a brute force / trial and error approach.

Match, Vlookup, and Hlookup with match-type set to 1/TRUE and Lookup by default do recruit an algorithm kindred to binary search, quite predctable in behavior:

See Post #7 in:

http://www.mrexcel.com/forum/excel-questions/310278-vlookup-multiple-matches-match-returned.html

 

[Firefly2012]

With the optional 4th argument set to True, Excel will use a binary search algorithm to identify matches. This explicitly requires a sorted array/range to work correctly - with anything else, you won't be able to guarantee the results.

 

[Aladin Akyurek]

With the optional 4th argument set to True, Excel will use a binary search algorithm to identify matches. This explicitly requires a sorted array/range to work correctly - with anything else, you won't be able to guarantee the results.

Firefly: I'd prefer saying that the algorithm operates correctly irrespective of the fact whether the target is sorted in ascending order or not.

 

[Firefly2012]

I bow to the master

You are right of course - the algorithm will work as designed, just maybe not as the user expects

 

[Gerald Higgins]

Thanks for the link Aladin, that's very interesting.

If we follow the logic in that link, I think I can see how the values for MARC and SASHA are derived.

For example, for MARC...
Step 1, split the look up table into two, Todd to Freddie, and Carla to Carl.
Because Carla <= MARC, take the second half.
Step 2, split that into two, Carla to Mike, Bill to Carl
Because Bill <= MARC, take the second half.
Step 3, split that into two, Bill to Sasha, Ron to Carl
Because Ron > MARC, take the first half.
Step 4, split that into two, Bill, and Sasha.
Because Sasha > Marc, return the value for Bill.

Similar logic applies for SASHA.

But I can't repeat the logic for FREDDIE.
It seems to me that because FREDDIE >= Carla, the value returned should be one from the second half of the table.

Am I missing something ?

Apologies if it's just me being thick.

 

[Monsignor]

This is fascinating. There does seem to be some significance in half-way point in the lookup list.

 

[Monsignor]

Gerald, I wasn't following your logic until I watched an animated video on Binary Search Algorithms. Now, I'm with you.
We get the right answer for Freddie because Freddie is the middle: comparison #1. (I think)