Drop down box and Lookup value problem

[corus]

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?

 

[BigInch]

There's always been problems with those lookup functions.
Adding numeric prefixes lets it work.

I hate Windowz 8!!!!

 

[PMR06]

You can try using VLOOKUP instead:

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

FALSE will force it to find an exact match.

 

[corus]

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.

 

[dik]

I don't know what you wanted to achieve... maybe as attached?

Dik

 

[dik]

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

 

[CarlB]

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.

 

[dik]

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

 

[IDS]

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/

 

[IDS]

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/

 

[dik]

Thanks, Doug...

Dik