0

I am using Vlookup example =VLOOKUP(E10,A18:B50,2) to select a description of a product but it works for some lines but not others.

When it doesn't work it tends to select the last descriptions for the product (ie field A18 will correctly select field B18 and so forth until it gets to say A25 which will incorrectly select B50 and so on). The product numbers are a little complex such as

ABD10050CNR-XX (ski041)

with a description of

Bench Duct Int/Ext Corner.

Is this not working because the product numbers and descriptions are to complex/messy?

1
  • 1
    Do you have a small example that is failing? Mar 18, 2013 at 23:59

4 Answers 4

6

try

=VLOOKUP(E10,A18:B50,2,False) 

If the fourth argument is omitted it defaults to true, and the lookup column needs to be sorted. See the Excel help:

If range_lookup is either TRUE or is omitted, an exact or approximate match is returned. If an exact match is not found, the next largest value that is less than lookup_value is returned.

[...]

If the range_lookup argument is FALSE, VLOOKUP will find only an exact match. If there are two or more values in the first column of table_array that match the lookup_value, the first value found is used. If an exact match is not found, the error value #N/A is returned.

So, omitting the fourth argument will never return an error value, but if the data is not sorted, it may return wrong results. If you want an exact match, use the fourth argument and set it to False.

0
1

Is the array range really A18:B50? Typically whenever I am using VLOOKUP, I'll do something like $A$18:$B$50 so that when I copy and paste my formula down, it will still keep the values in the array. You may be having an issue where your A25's value is higher up in the array and not found because your array is dynamically changing as you copy and paste down.

1
  • 1
    Thank you so much for your response. Solution provided by teylyn
    – richard
    Mar 19, 2013 at 2:13
0

The strictest rule for VLookup is that the table with the table MUST be sorted correctly for the key being used. If the table is NOT sorted, the last table value not greater than the search value will be supplied.

Example Table Sorted:

0 Red
1 Blue
3 Green

If the Value searched is '2' and it is missing, the next- lower value to the occurence of '3' would supply Blue as the answer.

Example Table Unsorted:

1 Blue
0 Red
3 Green

If the Value searched is '2' and it is missing, the next- lower value to the occurence of '3' would supply Red as the answer.

4
  • VLOOKUP does not require the table to be sorted. You're thinking about LOOKUP.
    – kobaltz
    Mar 18, 2013 at 23:54
  • I always sort my tables by habit. Sorry @koblaltz, I may be mixing my rules.
    – wbogacz
    Mar 18, 2013 at 23:56
  • 1
    If the fourth argument is omitted, the function will default it to TRUE, which in turn requires the data to be sorted for meaningful results.
    – teylyn
    Mar 19, 2013 at 0:13
  • Thank you so much for your response. Solution provided by teylyn
    – richard
    Mar 19, 2013 at 2:13
0

Another possible reason is that the searched text contains a tilde (~) which is an escape character for VLOOKUP, so that will probably not find anything, or not find what you expect.

See the explanation and solution in this other Super User question.

You must log in to answer this question.

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