Lookup problem

[Faithskeptic]

I'm trying to setup a simple spreadsheet in which I select a value from a
validation list box (cell A1), and after selecting from the list in cell A1,
a value is returned in cell B1 by using a the LOOKUP function. Below is an
example of what my spreadsheet looks like, with the LOOKUP formula in cell
B1.







A B

1 Validation =LOOKUP(A1,A2:A5,B2:B5)

2 Blue Black

3 Red Brown

4 Yellow Orange

5 Green Purple







The problem is that the wrong value is often being returned in cell B1 after
selecting from the list in A1. For example, if I select Blue from the
validation box in A1, Black is returned in B1...If I select Red in A1, Brown
is returned in B1...If I select Yellow in A1, Orange is returned in B1...So
far, so good. But if I select Green in A1, Brown is returned in B1, where
as Purple SHOULD be the returned value. I can't figure out why in most
cases, the correct value is being returned when selecting from the list, (as
it should be), and the wrong value is being returned in others. I've tried
rearranging my list, but for some reason, Green never returns the correct
value.



Any help in this matter would be greatly apprectiated.



D

 

[keithl816]

Hi D,

Try something like this ib cell b1

=IF(ISNA(VLOOKUP(A1,$A$2:$B$5,2,FALSE)),"0",VLOOKUP(A1,$A$2:$B$5,2,FALSE))

This should work for you

Larry

 

[Gord Dibben]

The lookup vector(A2:A5) must be in ascending order.

Change "green" to "zerk" and it will work.

Better to use a VLOOKUP formula which does not require sorting.

=VLOOKUP(A1,$A$2:$B$5,2,FALSE)


Gord Dibben Excel MVP