Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Lookup problem
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Lookup problem
Hi D, Try something like this ib cell b1 =IF(ISNA(VLOOKUP(A1,$A$2:$B$5,2,FALSE)),"0",VLOOKU P(A1,$A$2:$B$5,2,FALSE)) This should work for you Larry -- keithl816 ------------------------------------------------------------------------ keithl816's Profile: http://www.excelforum.com/member.php...o&userid=21287 View this thread: http://www.excelforum.com/showthread...hreadid=491664 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Lookup problem
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 On Wed, 7 Dec 2005 16:02:51 -0700, "Faithskeptic" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Range Lookup - Problem | Excel Worksheet Functions | |||
Range Lookup - Problem | Excel Worksheet Functions | |||
Range Lookup - Problem | Excel Worksheet Functions | |||
Lookup Problem | Excel Worksheet Functions | |||
Lookup Problem | Excel Worksheet Functions |