## 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

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