Weird VLOOKUP quirk?

[Sachin]

Today I was using VLOOKUP against a series of my organizations divisions. The last choice was "All" (no quotes) but the formula returned a 0 response. If I changed it to anything not beginning with All, the lookup worked perfectly. Has anyone else run into this?

 

[prasaddn]

Hi Sachin,

Can you provide more information of your data, usage of lookup values/parameters and what you mean by "All" I did not understand that.. sorry.


Regards,

Prasad DN

 

[SirJB7]

Hi, Sachin!

The VLookUp function doesn't retrieve a zero value (I assume from your post that you're searching for the division name), unless you've embedded it on a IF or other encapsulating formula. It just return the value searched (exact or aproximated, depending on last parameter false or true, respectively) or the #N/A text.

Despite of that, if you search for the unquoted value "All", it won't retrieve all your divisions (let's say three), it actually performs a search for a division named All.

Regards!

 

[Sachin]

I figured out my problem. My list went like this {Action, Ahoy, Attack, <Other Items>, All}. VLOOKUP was returning the relevant values from Ahoy because it was closest to All, which was at the bottom of the list. Setting the Range Lookup to FALSE (ensuring an exact match) fixed it.