Help with LOOKUP function

[hobbes02]

So I have a drop-down menu linked to 10 cells. Then i have another cell that uses the lookup function and the information from the drop down menu to find information in a database. Problem is the lookup function seems to be exploding if the value from the drop-down menu starts with 'd'. Also, it seems not to be working if two of the values from the drop-down menu are similar.

For example, I have (ECN, R&D, DRAWINGS, ECN1) in the drop down.

Look-up Formula: Lookup((drop-down cell), database column (a), database column (b).

If ECN or R&D is selected, the correct value comes back. If Drawings is selected, an NA is returned, and if ECN1 is selected the value from ECN is returned.

Maybe i'm using the wrong command, can anyone help?

 

[AlphaFrog]

Hi and welcome.

Try using the VLOOKUP function

=VLOOKUP(drop-down cell, database_columnA:B, 2, 0)


Or this to avoid an error when there isn't a match...

=IF(ISERROR(VLOOKUP(drop-down cell, database_columnA:B, 2,0)), "No Match", VLOOKUP(drop-down cell, database_columnA:B, 2,0))

 

[Jonmo1]

Welcome to the board.

Lookup uses "Closest match", and when dealing with TEXT, it get's wierd.

Try Index/Match instead, this can look for EXACT match..

using your psudo formula posted...
Lookup((drop-down cell), database column (a), database column (b).

Try
=INDEX(database column (b),MATCH((drop-down cell),database column (a),0))

 

[hobbes02]

Thanks for help everyone, we ended up using the HLOOKUP function just cause of the way the databased was set up, and it seems to be working now:

=HLOOKUP(drop-down cell,Database!$1:$21,6,FALSE)

By the way, I guess '0' is the same as 'FALSE'?