If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. 


Thread Tools  Display Modes 
#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 
Ads 
#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 
Thread Tools  
Display Modes  


Similar Threads  
Thread  Thread Starter  Forum  Replies  Last Post 
data validationmultiple dependent list  Michael  Excel Discussion (Misc queries)  9  May 2nd 06 01:14 AM 
Dependent List (via Data Validation) Error  Dezdan  Excel Worksheet Functions  2  December 2nd 05 12:33 AM 
custom dependent list validation  ben h  Excel Worksheet Functions  1  October 24th 05 05:31 AM 
Dependent List Data Validation  Annie  Excel Worksheet Functions  2  September 23rd 05 03:40 PM 
Using Validation List from Another Workbook with Dependent Data  Mike R.  Excel Worksheet Functions  5  January 8th 05 07:06 PM 