Lookup not returning correct values

[sirnose1]

When I choose a value from the dropdown ("(ALL)","Question 1a","Question 1b","Question 1c"), it gives me only the 1st or third ("1-Does the Person have an impairment that limits the use of a lower extremity","C1-No lower extremity involvement(STOP HERE)".

here is my formula. Can you tell me what I am doing wrong?

=LOOKUP(B1,{"(ALL)","Question 1a","Question 1b","Question 1c"}, {"1-Does the Person have an impairment that limits the use of a lower extremity","A1-Yes, one extremity","B1-Yes, both extremities","C1-No lower extremity involvement(STOP HERE)"})

 

[SkipVought]

hi,

In your Data Validation --LIST...
[tt]
(ALL),Question 1a,Question 1b,Question 1c
[/tt]
NO QUOTES!

However, I would strongly recommend against embedding your list. I ALWAYS use lists on the sheet. If you use a named range for you list, it can be on another sheet. then use a LOOKUP against the list to return your value.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[sirnose1]

Removing the quotes wont work here. I am using these lookup tables
Question1 Allowances Denials
Question1a 45 6
Question1b 71 9
Question1c 25 17


Questions Index
1a- Yes, one extremity 1
1b- Yes, both extremities 2
1c- No Lower Extremity Involvement (STOP HERE) 3

The allowances and denials are OK, it's the questions that wont update.

 

[SkipVought]

In your Data Validation -- LIST, remove the QUOTES.

That is the ONLY place to remove QUOTES.

Here are the DROP DOWN values and results from your formula copies one by one...
[tt]
(ALL) 1-Does the Person have an impairment that limits the use of a lower extremity
Question 1a A1-Yes, one extremity
Question 1b B1-Yes, both extremities
Question 1c C1-No lower extremity involvement(STOP HERE)
[/tt]
But AGAIN, I recommend NOT embedding in BOTH the DROP DOWN and the Lookup Formula.


Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[sirnose1]

But when I remove the quotes I get formula errors....

 

[SkipVought]

From exactly where did you remove the quotes?

Please copy 'n' paste the list(s) here, exactly as it is in your sheet.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[sirnose1]

With quotes:

=LOOKUP(B1,{"(ALL)","Question 1a","Question 1b","Question 1c"}, {"1-Does the Person have an impairment that limits the use of a lower extremity","A1-Yes, one extremity","B1-Yes, both extremities","C1-No lower extremity involvement(STOP HERE)"})

With quotes removed
=LOOKUP(B1,{(ALL),Question 1a,Question 1b,Question 1c}, {1-Does the Person have an impairment that limits the use of a lower extremity,A1-Yes, one extremity,B1-Yes, both extremities,C1-No lower extremity involvement(STOP HERE)})

Question list:

Questions Index
1a- Yes, one extremity 1
1b- Yes, both extremities 2
1c- No Lower Extremity Involvement (STOP HERE) 3


Question1 Allowances Denials
Question1a 45 6
Question1b 71 9
Question1c 25 17

 

[SkipVought]

Quoting my post of 10 May 11 14:57

In your Data Validation -- LIST, remove the QUOTES.

That is the ONLY place to remove QUOTES.

So WHY did you remove the QUOTES from your formula???

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[sirnose1]

There are no quotes in these list.

 

[SkipVought]

When I choose a value from the dropdown ("(ALL)","Question 1a","Question 1b","Question 1c"),

GET RID OF THE QUOTES IN THE DROPDOWN.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}