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?
Talk To Other Members
RE: Drop down box and Lookup value problem
Adding numeric prefixes lets it work.
I hate Windowz 8!!!!
RE: Drop down box and Lookup value problem
=VLOOKUP(A7,A1:B4,2,FALSE)
FALSE will force it to find an exact match.
RE: Drop down box and Lookup value problem
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
Dik
RE: Drop down box and Lookup value problem
Dik
RE: Drop down box and Lookup value problem
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
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
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
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
Dik