Excel failing at String VLOOKUP
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; otherwiseVLOOKUP
may not give the correct value. Ifrange_lookup
is FALSE, table_array does not need to be sorted.
And also:
range_lookup
is a logical value that specifies whether you wantVLOOKUP
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 thanlookup_value
is returned. IfFALSE
,VLOOKUP
will find an exact match. If one is not found, the error value#N/A
is returned.
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()