Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I have a worksheet that's returning unexpected VLOOKUP results, and I'm trying to narrow down the issue. I've run into a question I can't answer in my search for the solution.

Consider: I have a lookup field that contains a list of Countries. If one of the entries in the list is Great Britain, does VLOOKUP analyze the ENTIRE string in the cell? or just the first word?

Consider Further: If I have United States in a cell, and I'm comparing that value to my VLOOKUP set, what happens when I encounter United Arab Emirates? Does the lookup stop because it matched United? or does it continue because it's trying to match the String United States against a lookup value of United States?

Is it best to enclose lookup terms that include spaces in single or double quotes, or even do a comparison based on cell values with spaces stripped out of them?

share|improve this question
up vote 5 down vote accepted

VLOOKUP have 2 match modes - exact and approximate. They have many details greatly listed in the origin: VLOOKUP Help. However, 2 qoutes are quite relevant to the question:

When searching text values in the first column of table_array, ensure that the data in the first column of table_array does not have leading spaces, trailing spaces, inconsistent use of straight ( ' or " ) and curly ( ‘ or “) quotation marks, or nonprinting characters. In these cases, VLOOKUP may give an incorrect or unexpected value.

And one more:

If range_lookup is FALSE and lookup_value is text, then you can use the wildcard characters, question mark (?) and asterisk (*), in lookup_value. A question mark matches any single character; an asterisk matches any sequence of characters. If you want to find an actual question mark or asterisk, type a tilde (~) preceding the character.

So, depending on what are your needs, just specify desired options and use wildcards as described. Hope that was helpful!

share|improve this answer
2  
wow, didn't know you can use wildcards in VLOOKUP/MATCH! +1! (So you can catch up here! ;-) ) – Peter Albert Feb 15 '13 at 23:36

It seems like you simply forgot to provide FALSE (or 0 for the short version) as the 4th parameter of VLOOKUP. This will set it to exact match mode, i.e. it'll check each entry in your first column for a full match.

If you leave out the FALSE, Excel assumes your first column is sorted - and applies a binary search algorithm that is much more efficient - but will deliver the wrong results if its not sorted or if the element is not contained in the list. For further details see my explanations here.

share|improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .