Home 
Search 
Today's Posts 
#1




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 
#2




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 
#3




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 
#4




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 
#5




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 
#6




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 
#7




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 
Reply 
Thread Tools  Search this Thread 
Display Modes  


Similar Threads  
Thread  Forum  
data validationmultiple dependent list  Excel Discussion (Misc queries)  
Dependent List (via Data Validation) Error  Excel Worksheet Functions  
custom dependent list validation  Excel Worksheet Functions  
Dependent List Data Validation  Excel Worksheet Functions  
Using Validation List from Another Workbook with Dependent Data  Excel Worksheet Functions 