Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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?

share|improve this question
1  
Do you have a small example that is failing? –  Brad Patton Mar 18 '13 at 23:59
add comment

3 Answers

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.

share|improve this answer
    
Thank you so much - fixed with False! Happy days!! –  richard Mar 19 '13 at 2:08
add comment

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.

share|improve this answer
1  
Thank you so much for your response. Solution provided by teylyn –  richard Mar 19 '13 at 2:13
add comment

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.

share|improve this answer
    
VLOOKUP does not require the table to be sorted. You're thinking about LOOKUP. –  kobaltz Mar 18 '13 at 23:54
    
I always sort my tables by habit. Sorry @koblaltz, I may be mixing my rules. –  wbogacz Mar 18 '13 at 23:56
    
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 '13 at 0:13
    
Thank you so much for your response. Solution provided by teylyn –  richard Mar 19 '13 at 2:13
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.