1. ## VLOOKUP weirdness (2000)

The short version: VLOOKUP giving me the wrong, uh, look up ... thingy. Where can I learn more about how this most mysterious function actually works?

The Long version: My expense claim sheet uses VLOOKUP to provide me with the tax code for a selected province (or state if need be). It finds the province, then displays the tax code from the table. Pretty simple, eh? Well, I'm stymied (fortunately, this is where I spend a lot of time these days so I'm pretty used to it). For some reason, VLOOKUP was giving me the tax code for the province listed above the one I wanted. What's up with that?

It gets weirder:
When I pulled the orginal equation, I managed to have too many parentheses. I figured it wouldn't matter -- after all, =VLOOKUP(A1,PST,2) and =((VLOOKUP(A1,PST,2))) should equate the same, no? Well, I don't know what happened, but it didn't. I deleted the parentheses and it started to work a bit better.

A solution?
As I messed around with the equation and the table array, I discovered that sorting the array made a difference. I couldn't figure out the logic, but so long as the alpha-order was in place, I got the right result. I had tried to add the FALSE parameter earlier, but it didn't take for some reason. I tried it again later and =VLOOKUP(A1,PST,2,FALSE) seemed to do the trick.

I spent two hours on this problem yesterday, another two this morning. I've pretty much resolved the issue as I've talked through it here and experimented a bit more (hey, it would have cost me \$300 had this been a therapy session!). The Help file isn't that helpful (what else is new?), so if you can direct me to a good on-line resource that explains the mysterious ways of VLOOKUP, I'd be mighty thankful.

Speaking of thankful, if you need a multi-currency expense claim, some day I might actually have one that I'll share with anyone who needs it.

2. ## Re: VLOOKUP weirdness (2000)

In it's own arcane way, the Help on the last parameter to
VLOOKUP(lookup_value,table_array,col_index_num,ran ge_lookup)
is essentially saying that if you have unsorted data or want an exact match only, you must change the last parameter from the default TRUE to FALSE.

3. ## Re: VLOOKUP weirdness (2000)

To expand on what John said:
Unsorted data will rarely (if ever) give you the correct result if you do not add the FALSE parameter.

The way excel searches thru the list is entirely different if you choose False or True (True si same as none entered)

The way to imagine it (I don't know the exact alogorithm it uses) is when you have entered FALSE, excel will start at the first item and go down the list (checking every item) If/when it finds a match it stops, goes over the appropriate number of columns and gives the results. It will always find the FIRST match if you have more than 1 exact match. If you have no match it will give an error.

If you have the parameter to TRUE (or not entered), you have told excel that the list is sorted, so excel can use faster algorithms than searching thru it item by item. It will start in the middle and see if that item is bigger or smaller than the search item. If larger it knows it can ignore everything before that item, if smaller, it can ignore everything after that item.

It then splits the difference from the middle to the end (or beginning depending on result: greater or smaller). It continuses in this manner, eliminating half the answers with each test. (a list of 1000 items can be searched by looking at only 7 matches with this scheme!). If your list is not sorted, excel can thus find "weird results" since it only looks until your number finds a "near match" and it can actually eliminate the true match in an early "test" due to this scheme.

Steve

4. ## Ahhh!

Too bad then that it's not defaulted to FALSE instead of TRUE. Thanks!

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•