A Microsoft Excel forum. ExcelBanter

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.

Go Back   Home » ExcelBanter forum » Excel Newsgroups » Excel Worksheet Functions
Site Map Home Register Authors List Search Today's Posts Mark Forums Read Web Partners

If, vlookup, data validation & dependent list



 
 
Thread Tools Display Modes
  #1  
Old July 11th 07, 02:14 PM posted to microsoft.public.excel.worksheet.functions
Karen
external usenet poster
 
Posts: 447
Default 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  
Old July 11th 07, 02:26 PM posted to microsoft.public.excel.worksheet.functions
Bob Phillips
external usenet poster
 
Posts: 10,594
Default 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  
Old July 11th 07, 02:28 PM posted to microsoft.public.excel.worksheet.functions
bj
external usenet poster
 
Posts: 1,397
Default 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  
Old July 11th 07, 02:28 PM posted to microsoft.public.excel.worksheet.functions
Toppers
external usenet poster
 
Posts: 4,340
Default 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  
Old July 11th 07, 02:46 PM posted to microsoft.public.excel.worksheet.functions
Karen
external usenet poster
 
Posts: 447
Default 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  
Old July 11th 07, 02:56 PM posted to microsoft.public.excel.worksheet.functions
Teethless mama
external usenet poster
 
Posts: 3,718
Default 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  
Old July 11th 07, 04:18 PM posted to microsoft.public.excel.worksheet.functions
Bob Phillips
external usenet poster
 
Posts: 10,594
Default 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

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
data validation--multiple 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 01: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 08:06 PM


All times are GMT +1. The time now is 05:49 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2014, Jelsoft Enterprises Ltd.
Copyright 2004-2014 ExcelBanter.
The comments are property of their posters.