INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Drop down box and Lookup value problem

Drop down box and Lookup value problem

(OP)
The attached spreadsheet contains a simple drop down box to select a material type and from that selection a Lookup function is used to obtain the corresponding material property for that type. The problem with it is that it works for all the materials except the last one listed, ie. material 'Titanium, 10% Vanadium' where it incorrectly selects the material for type 'Tin'. I've found that by editing the name slightly that it'll work but can't figure out why it would mistake 'Titanium, 10% Vanadium' for 'Tin' as originally input. I came across this error by chance as the original spreadsheet contains a long list of materials, and yet it always chose 'Tin' when selecting material 'Titanium, 10% Vanadium'. Any ideas as to why it wouldn't work?

RE: Drop down box and Lookup value problem

You can try using VLOOKUP instead:

=VLOOKUP(A7,A1:B4,2,FALSE)

FALSE will force it to find an exact match.

RE: Drop down box and Lookup value problem

(OP)
Thanks.
I eventually found a link that sort of explains the problem - http://www.excelfunctions.net/ExcelLookup.html though I'm not too sure as to the exact cause in the data. Must have been a hidden character or something to cause it to hiccup as the original data was cut and pasted from a web site.

RE: Drop down box and Lookup value problem

I copied the data... added an index and then sorted on the last three columns A=>Z... and at least does what I wanted it to. By copying the data into an adjacent column the new column is 'exactly' the same and you have to sort in order for vlookup to work.

Dik

RE: Drop down box and Lookup value problem

I'd say the problem is the list is not alphabetical.
Put 'Titanium, 10% Vanadium' as the second item then it works.

So better to use the vlookup method suggested.

RE: Drop down box and Lookup value problem

CarlB...

by using two lists, your dropdown list can be in a logical order while your sorted table is sorted on the first column as required by VLOOKUP... I use an NDX column in case I have to add an item... I resort the list based on the index and add the item into both the dropdown list and the new table... I modify the indices, and I then resort it based on the new first column. For NDX, I use cell + 1 to automatically increment the index and than use copy-paste special to convert to real integers.

by copying the dropdown items directly there is an 'exact' copy of the dropdown list to sort.

Dik

RE: Drop down box and Lookup value problem

It doesn't need two lists, and the cause isn't mysterious. As PMR06 and CarlB have suggested, using VLookup with a final FALSE (or 0) argument will force an exact match and return the correct result.

By default Vlookup assumes the data is in ascending order, and will return the value before the first value that is greater than the lookup value. For instance, if your lookup table has: Arthur, Bert, Charles, Brian then:

=VLOOKUP("Brian", datarange,1) will return Bert
but =VLOOKUP("Brian", datarange,1, False) will return Brian

So the message is:
Use VLookup, rather than Lookup
If you want an exact match, always add the FALSE argument to make sure you get it (even if the data is supposed to be in alphabetical order, it may not be).

Doug Jenkins
Interactive Design Services
http://newtonexcelbach.wordpress.com/

RE: Drop down box and Lookup value problem

Quote:

I eventually found a link that sort of explains the problem - http://www.excelfunctions.net/ExcelLookup.html though I'm not too sure as to the exact cause in the data. Must have been a hidden character or something to cause it to hiccup as the original data was cut and pasted from a web site.

What that site doesn't say (which the Excel help does) is that the LOOKUP function is only provided for backward compatibility. For new work always use VLOOKUP instead, with the FALSE argument, where required.

Doug Jenkins
Interactive Design Services
http://newtonexcelbach.wordpress.com/

RE: Drop down box and Lookup value problem

Thanks, Doug...

Dik

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members!


Resources


Close Box

Join Eng-Tips® Today!

Join your peers on the Internet's largest technical engineering professional community.
It's easy to join and it's free.

Here's Why Members Love Eng-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close