Menu
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.
596
Text values or data types do not match Another common reason for VLOOKUP failure is the difference between your lookup value and a similar value in the lookup column. In some cases, the difference is so subtle that it's hard to spot visually.
Yes. VLOOKUP can search for textual values just as well as it can search for numbers. The example above would search for the product names, which are text rather than numbers.
Solution: If you are sure the relevant data exists in your spreadsheet and VLOOKUP is not catching it, take time to verify that the referenced cells don't have hidden spaces or non-printing characters. Also, ensure that the cells follow the correct data type.
VLOOKUP Returns a value but it is incorrect There are a number of possible reasons for this: You didn't specify whether Excel must do an Exact or Approximate match (true or false as the last criteria) You specified that Excel does an approximate match but you should have required an exact match.
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_lookupis 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; otherwiseVLOOKUPmay not give the correct value. Ifrange_lookupis FALSE, table_array does not need to be sorted.
And also:
range_lookupis a logical value that specifies whether you wantVLOOKUPto 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_valueis returned. IfFALSE,VLOOKUPwill find an exact match. If one is not found, the error value#N/Ais returned.
143
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()
36
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
