Excel failing at String VLOOKUP

Question

I have a table that looks like this

   B   C
43 XS  6
44 S   11
45 M   16
46 L   21
47 XL  26
48 XXL 31

I've written the formula:

`VLOOKUP("S",B43:C48,2)`

It's returning a value of 21. WHY?! It should be returning 11!

update I reproduced this exact error in a different table. VLOOKUP works when the search value is a number, but consistently fails when I use strings.

Answer 1

VLOOKUP does strange things unless you specify an "exact match" with the fourth argument, like so:

=VLOOKUP("S",B43:C48,2,FALSE)

From Excel's help file:

VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)

If range_lookup is TRUE, the values in the first column of table_array must be placed in ascending order: ..., -2, -1, 0, 1, 2, ..., A-Z, FALSE, TRUE; otherwise VLOOKUP may not give the correct value. If range_lookup is FALSE, table_array does not need to be sorted.

And also:

range_lookup is a logical value that specifies whether you want VLOOKUP to find an exact match or an approximate match. If TRUE or omitted, an approximate match is returned. In other words, if an exact match is not found, the next largest value that is less than lookup_value is returned. If FALSE, VLOOKUP will find an exact match. If one is not found, the error value #N/A is returned.

Answer 2

Also, mismatching format types is causing you problems. (One cell is formatted as containing numbers, and the other is formatted as containing text). See problem/solution #2 on this page:

Solutions to Three Common Problems when Using VLOOKUP()