If, vlookup, data validation & dependent list
 

I have a data validation selection in A2 and a dependent list in A3. There
are instances where there's no dependent list after making a selection in A2;
so there's no entry made in A3.

I want to a formula in A4. The formula will say if A3 is not blank, vlookup
A3 in a lookup table. However, if A3 is blank, vlookup A2 in the same lookup
table. My formula currently reads like this: =IF(A3<"
",VLOOKUP(A3,Sheet1!$A$2:$B$141,2,FALSE),VLOOKUP(A 2,Sheet1!$A$2:$B$141,2,FALSE)).

The trouble is when A3 is blank I get a #N/A result even though I know that
A2 is in the table. Please advise as to how to modify this formula to get
the correct result. All help is prematurely appreciated.
--
Thanks, Karen

If, vlookup, data validation & dependent list
 

Karen,

Try this

=IF(A3<"",VLOOKUP(A3,Sheet1!$A$2:$B$141,2,FALSE), "")&IF(A3="",VLOOKUP(A2,Sheet1!$A$2:$B$141,2,FALSE ),"")

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Karen" wrote in message
...
I have a data validation selection in A2 and a dependent list in A3.
There
are instances where there's no dependent list after making a selection in
A2;
so there's no entry made in A3.

I want to a formula in A4. The formula will say if A3 is not blank,
vlookup
A3 in a lookup table. However, if A3 is blank, vlookup A2 in the same
lookup
table. My formula currently reads like this: =IF(A3<"
",VLOOKUP(A3,Sheet1!$A$2:$B$141,2,FALSE),VLOOKUP(A 2,Sheet1!$A$2:$B$141,2,FALSE)).

The trouble is when A3 is blank I get a #N/A result even though I know
that
A2 is in the table. Please advise as to how to modify this formula to get
the correct result. All help is prematurely appreciated.
--
Thanks, Karen

If, vlookup, data validation & dependent list
 

I am not sure but I think you have " " and not "" in your formula
if so change to "" and it should work

"Karen" wrote:

I have a data validation selection in A2 and a dependent list in A3. There
are instances where there's no dependent list after making a selection in A2;
so there's no entry made in A3.

I want to a formula in A4. The formula will say if A3 is not blank, vlookup
A3 in a lookup table. However, if A3 is blank, vlookup A2 in the same lookup
table. My formula currently reads like this: =IF(A3<"
",VLOOKUP(A3,Sheet1!$A$2:$B$141,2,FALSE),VLOOKUP(A 2,Sheet1!$A$2:$B$141,2,FALSE)).

The trouble is when A3 is blank I get a #N/A result even though I know that
A2 is in the table. Please advise as to how to modify this formula to get
the correct result. All help is prematurely appreciated.
--
Thanks, Karen

If, vlookup, data validation & dependent list
 

Formula is OK

=IF(A3<"",VLOOKUP(A3,Sheet1!$A$2:$B$141,2,FALSE), VLOOKUP(A2,Sheet1!$A$2:$B$141,2,FALSE))

Is A3 test this: A3<"" OR this A3<" "; the latter will give #N/A as it
will search on A3 not A2.

Check A2 data and Sheet1 Column A have no extra blanks in them.

"Karen" wrote:

I have a data validation selection in A2 and a dependent list in A3. There
are instances where there's no dependent list after making a selection in A2;
so there's no entry made in A3.

I want to a formula in A4. The formula will say if A3 is not blank, vlookup
A3 in a lookup table. However, if A3 is blank, vlookup A2 in the same lookup
table. My formula currently reads like this: =IF(A3<"
",VLOOKUP(A3,Sheet1!$A$2:$B$141,2,FALSE),VLOOKUP(A 2,Sheet1!$A$2:$B$141,2,FALSE)).

The trouble is when A3 is blank I get a #N/A result even though I know that
A2 is in the table. Please advise as to how to modify this formula to get
the correct result. All help is prematurely appreciated.
--
Thanks, Karen

If, vlookup, data validation & dependent list
 

Thanks all for the assistance. It works well now.
--
Thanks, Karen


"Bob Phillips" wrote:

Karen,

Try this

=IF(A3<"",VLOOKUP(A3,Sheet1!$A$2:$B$141,2,FALSE), "")&IF(A3="",VLOOKUP(A2,Sheet1!$A$2:$B$141,2,FALSE ),"")

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Karen" wrote in message
...
I have a data validation selection in A2 and a dependent list in A3.
There
are instances where there's no dependent list after making a selection in
A2;
so there's no entry made in A3.

I want to a formula in A4. The formula will say if A3 is not blank,
vlookup
A3 in a lookup table. However, if A3 is blank, vlookup A2 in the same
lookup
table. My formula currently reads like this: =IF(A3<"
",VLOOKUP(A3,Sheet1!$A$2:$B$141,2,FALSE),VLOOKUP(A 2,Sheet1!$A$2:$B$141,2,FALSE)).

The trouble is when A3 is blank I get a #N/A result even though I know
that
A2 is in the table. Please advise as to how to modify this formula to get
the correct result. All help is prematurely appreciated.
--
Thanks, Karen

If, vlookup, data validation & dependent list
 

Simplify version:

=VLOOKUP(IF(OR(A3={""," "}),A2,A3),Sheet1!$A$2:$B$141,2,0)


"Karen" wrote:

I have a data validation selection in A2 and a dependent list in A3. There
are instances where there's no dependent list after making a selection in A2;
so there's no entry made in A3.

I want to a formula in A4. The formula will say if A3 is not blank, vlookup
A3 in a lookup table. However, if A3 is blank, vlookup A2 in the same lookup
table. My formula currently reads like this: =IF(A3<"
",VLOOKUP(A3,Sheet1!$A$2:$B$141,2,FALSE),VLOOKUP(A 2,Sheet1!$A$2:$B$141,2,FALSE)).

The trouble is when A3 is blank I get a #N/A result even though I know that
A2 is in the table. Please advise as to how to modify this formula to get
the correct result. All help is prematurely appreciated.
--
Thanks, Karen

If, vlookup, data validation & dependent list
 

Surely TRIM is better in a 'simplified' version?

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Teethless mama" wrote in message
...
Simplify version:

=VLOOKUP(IF(OR(A3={""," "}),A2,A3),Sheet1!$A$2:$B$141,2,0)


"Karen" wrote:

I have a data validation selection in A2 and a dependent list in A3.
There
are instances where there's no dependent list after making a selection in
A2;
so there's no entry made in A3.

I want to a formula in A4. The formula will say if A3 is not blank,
vlookup
A3 in a lookup table. However, if A3 is blank, vlookup A2 in the same
lookup
table. My formula currently reads like this: =IF(A3<"
",VLOOKUP(A3,Sheet1!$A$2:$B$141,2,FALSE),VLOOKUP(A 2,Sheet1!$A$2:$B$141,2,FALSE)).

The trouble is when A3 is blank I get a #N/A result even though I know
that
A2 is in the table. Please advise as to how to modify this formula to
get
the correct result. All help is prematurely appreciated.
--
Thanks, Karen