1

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?

2 Answers 2

5

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!

1
  • 2
    wow, didn't know you can use wildcards in VLOOKUP/MATCH! +1! (So you can catch up here! ;-) ) Feb 15, 2013 at 23:36
0

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.

You must log in to answer this question.

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