Look Ups Next Cells

[trainer75]

Hi,

Does anyone have a clue to why my look up is not find the correct data?

On my spreadsheet in column D I have a list of media (ie CAGE, PALLET, TOTE etc) and in column E I have a cumber which references what row they are in on another tab, I then pick which media by a drop down box and then I want the number to show in reference to the row.

Example
Column D Column E
CAGE 4
PALLET 5
TOTE 6
BARREL 7
TROLLEY 8

A7 is a drop down box where I can choose the media

The formula I have is
=LOOKUP(A7,D1:D7,E1:E7)


Unfortunately the numbers don't match the media and I cant fathom out why, so if I want to select barrel I want the number 7 to show.

Hope you can help

 

[Sixthsense]

Use Vlookup() instead of Lookup() since it seems that you are not familiar with Lookup() function.

=VLOOKUP(A7,D1:E7,2,0)

Or

=LOOKUP(2,1/(D1:D7=A7),E1:E7)

 

[Peter_SSs]

From help

For the LOOKUP function to work correctly, the data being looked up must be sorted in ascending order. If this is not possible, consider using the VLOOKUP, HLOOKUP, or MATCH functions

 

[trainer75]

Use Vlookup() instead of Lookup() since it seems that you are not familiar with Lookup() function.

=VLOOKUP(A7,D1:E7,2,0)

Or

=LOOKUP(2,1/(D1:D7=A7),E1:E7)

Hi Thanks,

This has solved it

All the best