VLOOKUP + IF STATEMENTS

Received the following reply to earlier question (below).
Doesn't work. Can the above two statements be combined?
Ie. If one cell (in another worksheet) = "Active" then
lookup the values from the same worksheet.

I've been trying:
VLOOKUP(IF('Entry 1 - New Clients'!N2:N31="ACTIVE",'Entry
1 - New Clients'!A2,'Entry 1 - New Clients'!
A2:O20,2,FALSE))

Returns ugly errors. Maybe Excel doesn't like the
combination of both statements?

>>>>>>>>>>>>>>
Yes, just re-use the formula in another cell with a
different column index

=VLOOKUP('Entry 1 - New Clients'!A2,'Entry 1 - New
Clients'!A2:O20,3,FALSE).

note the ,3 not ,2

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Fiona" <[email protected]> wrote in
message
news:[email protected]...
> Received advise previously on this (thanks). However, I
> need to look up entries in another separate worksheet
and
> it won't work. I'm using:
>
> =VLOOKUP('Entry 1 - New Clients'!A2,'Entry 1 - New
> Clients'!A2:O20,2,FALSE). It returns the persons name
> (great) but I need to transfer across other details too.
>
> Basically I'm summarising one worksheet onto a specified
> format in another. In addition, I somehow want it to
only
> show me those clients in 'Entry 1 - New Clients' if the
> corresponding column (N) has the entry of "Active".
>
> Is this possible??
> Fiona


..

Hi
Not an expert but maybe:
=IF(Entry 1 - New Clients!$N$2="Active",VLOOKUP(Entry 1 - New Clients!$A2,Entry 1 - New Clients!$A$2:$O$20,COLUMN(B:B),0),"")


HTH
JG

Hi!

You need an entirely different formula because you're
doing a multiple criteria lookup.

Man, I hate long sheet names! <g>

It looks to me as though you want to lookup a clients name
in column A then lookup "active" in column N and return
the corresponding value in column B?

Something like this entered with the key combo of
CTRL,SHIFT,ENTER:

=IF(ISNA(MATCH(1,(A2:A11=A2)*(N2:N11="active"),0)),"",INDEX
(B2:B11,MATCH(1,(A2:A11=A2)*(N2:N11="active"),0)))

Just think how much longer that formula will get once you
put those long sheet names in! <g>

Biff

>-----Original Message-----
>Received the following reply to earlier question (below).
>Doesn't work. Can the above two statements be combined?
>Ie. If one cell (in another worksheet) = "Active" then
>lookup the values from the same worksheet.
>
>I've been trying:
>VLOOKUP(IF('Entry 1 - New Clients'!N2:N31="ACTIVE",'Entry
>1 - New Clients'!A2,'Entry 1 - New Clients'!
>A2:O20,2,FALSE))
>
>Returns ugly errors. Maybe Excel doesn't like the
>combination of both statements?
>
>>>>>>>>>>>>>>>
>Yes, just re-use the formula in another cell with a
>different column index
>
>=VLOOKUP('Entry 1 - New Clients'!A2,'Entry 1 - New
>Clients'!A2:O20,3,FALSE).
>
>note the ,3 not ,2
>
>--
>
>HTH
>
>RP
>(remove nothere from the email address if mailing direct)
>
>
>"Fiona" <[email protected]> wrote in
>message
>news:[email protected]...
>> Received advise previously on this (thanks). However, I
>> need to look up entries in another separate worksheet
>and
>> it won't work. I'm using:
>>
>> =VLOOKUP('Entry 1 - New Clients'!A2,'Entry 1 - New
>> Clients'!A2:O20,2,FALSE). It returns the persons name
>> (great) but I need to transfer across other details too.
>>
>> Basically I'm summarising one worksheet onto a specified
>> format in another. In addition, I somehow want it to
>only
>> show me those clients in 'Entry 1 - New Clients' if the
>> corresponding column (N) has the entry of "Active".
>>
>> Is this possible??
>> Fiona
>
>
>..
>
>
>.
>

I agree with you about how the long worksheet names clutter up the
formula--especially when you're creating it.

I like to rename the long worksheet name to A. Then build the formula. Then
after it's perfect, I change the name of the worksheet.

This works fine until I get to a formula that will be longer than 1024
characters (measured in R1C1 reference style). But I'd have that problem anyway
with the long worksheet name.

(More for the OP, than you, Biff. <bg>)

Biff wrote:
>
> Hi!
>
> You need an entirely different formula because you're
> doing a multiple criteria lookup.
>
> Man, I hate long sheet names! <g>
>
> It looks to me as though you want to lookup a clients name
> in column A then lookup "active" in column N and return
> the corresponding value in column B?
>
> Something like this entered with the key combo of
> CTRL,SHIFT,ENTER:
>
> =IF(ISNA(MATCH(1,(A2:A11=A2)*(N2:N11="active"),0)),"",INDEX
> (B2:B11,MATCH(1,(A2:A11=A2)*(N2:N11="active"),0)))
>
> Just think how much longer that formula will get once you
> put those long sheet names in! <g>
>
> Biff
>
> >-----Original Message-----
> >Received the following reply to earlier question (below).
> >Doesn't work. Can the above two statements be combined?
> >Ie. If one cell (in another worksheet) = "Active" then
> >lookup the values from the same worksheet.
> >
> >I've been trying:
> >VLOOKUP(IF('Entry 1 - New Clients'!N2:N31="ACTIVE",'Entry
> >1 - New Clients'!A2,'Entry 1 - New Clients'!
> >A2:O20,2,FALSE))
> >
> >Returns ugly errors. Maybe Excel doesn't like the
> >combination of both statements?
> >
> >>>>>>>>>>>>>>>
> >Yes, just re-use the formula in another cell with a
> >different column index
> >
> >=VLOOKUP('Entry 1 - New Clients'!A2,'Entry 1 - New
> >Clients'!A2:O20,3,FALSE).
> >
> >note the ,3 not ,2
> >
> >--
> >
> >HTH
> >
> >RP
> >(remove nothere from the email address if mailing direct)
> >
> >
> >"Fiona" <[email protected]> wrote in
> >message
> >news:[email protected]...
> >> Received advise previously on this (thanks). However, I
> >> need to look up entries in another separate worksheet
> >and
> >> it won't work. I'm using:
> >>
> >> =VLOOKUP('Entry 1 - New Clients'!A2,'Entry 1 - New
> >> Clients'!A2:O20,2,FALSE). It returns the persons name
> >> (great) but I need to transfer across other details too.
> >>
> >> Basically I'm summarising one worksheet onto a specified
> >> format in another. In addition, I somehow want it to
> >only
> >> show me those clients in 'Entry 1 - New Clients' if the
> >> corresponding column (N) has the entry of "Active".
> >>
> >> Is this possible??
> >> Fiona
> >
> >
> >..
> >
> >
> >.
> >

--

Dave Peterson

Hi Dave!

>I like to rename the long worksheet name to A. Then
>build the formula. Then after it's perfect, I change the
>name of the worksheet.

That's a good idea!

Biff

>-----Original Message-----
>I agree with you about how the long worksheet names
clutter up the
>formula--especially when you're creating it.
>
>I like to rename the long worksheet name to A. Then
build the formula. Then
>after it's perfect, I change the name of the worksheet.
>
>This works fine until I get to a formula that will be
longer than 1024
>characters (measured in R1C1 reference style). But I'd
have that problem anyway
>with the long worksheet name.
>
>(More for the OP, than you, Biff. <bg>)
>
>Biff wrote:
>>
>> Hi!
>>
>> You need an entirely different formula because you're
>> doing a multiple criteria lookup.
>>
>> Man, I hate long sheet names! <g>
>>
>> It looks to me as though you want to lookup a clients
name
>> in column A then lookup "active" in column N and return
>> the corresponding value in column B?
>>
>> Something like this entered with the key combo of
>> CTRL,SHIFT,ENTER:
>>
>> =IF(ISNA(MATCH(1,(A2:A11=A2)*
(N2:N11="active"),0)),"",INDEX
>> (B2:B11,MATCH(1,(A2:A11=A2)*(N2:N11="active"),0)))
>>
>> Just think how much longer that formula will get once
you
>> put those long sheet names in! <g>
>>
>> Biff
>>
>> >-----Original Message-----
>> >Received the following reply to earlier question
(below).
>> >Doesn't work. Can the above two statements be combined?
>> >Ie. If one cell (in another worksheet) = "Active" then
>> >lookup the values from the same worksheet.
>> >
>> >I've been trying:
>> >VLOOKUP(IF('Entry 1 - New Clients'!
N2:N31="ACTIVE",'Entry
>> >1 - New Clients'!A2,'Entry 1 - New Clients'!
>> >A2:O20,2,FALSE))
>> >
>> >Returns ugly errors. Maybe Excel doesn't like the
>> >combination of both statements?
>> >
>> >>>>>>>>>>>>>>>
>> >Yes, just re-use the formula in another cell with a
>> >different column index
>> >
>> >=VLOOKUP('Entry 1 - New Clients'!A2,'Entry 1 - New
>> >Clients'!A2:O20,3,FALSE).
>> >
>> >note the ,3 not ,2
>> >
>> >--
>> >
>> >HTH
>> >
>> >RP
>> >(remove nothere from the email address if mailing
direct)
>> >
>> >
>> >"Fiona" <[email protected]> wrote in
>> >message
>> >news:[email protected]...
>> >> Received advise previously on this (thanks).
However, I
>> >> need to look up entries in another separate worksheet
>> >and
>> >> it won't work. I'm using:
>> >>
>> >> =VLOOKUP('Entry 1 - New Clients'!A2,'Entry 1 - New
>> >> Clients'!A2:O20,2,FALSE). It returns the persons name
>> >> (great) but I need to transfer across other details
too.
>> >>
>> >> Basically I'm summarising one worksheet onto a
specified
>> >> format in another. In addition, I somehow want it to
>> >only
>> >> show me those clients in 'Entry 1 - New Clients' if
the
>> >> corresponding column (N) has the entry of "Active".
>> >>
>> >> Is this possible??
>> >> Fiona
>> >
>> >
>> >..
>> >
>> >
>> >.
>> >
>
>--
>
>Dave Peterson
>.
>

Legendary - works perfectly (apart from the enormous
statement this ends up being)

F
>-----Original Message-----
>Hi!
>
>You need an entirely different formula because you're
>doing a multiple criteria lookup.
>
>Man, I hate long sheet names! <g>
>
>It looks to me as though you want to lookup a clients
name
>in column A then lookup "active" in column N and return
>the corresponding value in column B?
>
>Something like this entered with the key combo of
>CTRL,SHIFT,ENTER:
>
>=IF(ISNA(MATCH(1,(A2:A11=A2)*
(N2:N11="active"),0)),"",INDEX
>(B2:B11,MATCH(1,(A2:A11=A2)*(N2:N11="active"),0)))
>
>Just think how much longer that formula will get once
you
>put those long sheet names in! <g>
>
>Biff
>
>>-----Original Message-----
>>Received the following reply to earlier question
(below).
>>Doesn't work. Can the above two statements be combined?
>>Ie. If one cell (in another worksheet) = "Active" then
>>lookup the values from the same worksheet.
>>
>>I've been trying:
>>VLOOKUP(IF('Entry 1 - New Clients'!
N2:N31="ACTIVE",'Entry
>>1 - New Clients'!A2,'Entry 1 - New Clients'!
>>A2:O20,2,FALSE))
>>
>>Returns ugly errors. Maybe Excel doesn't like the
>>combination of both statements?
>>
>>>>>>>>>>>>>>>>
>>Yes, just re-use the formula in another cell with a
>>different column index
>>
>>=VLOOKUP('Entry 1 - New Clients'!A2,'Entry 1 - New
>>Clients'!A2:O20,3,FALSE).
>>
>>note the ,3 not ,2
>>
>>--
>>
>>HTH
>>
>>RP
>>(remove nothere from the email address if mailing
direct)
>>
>>
>>"Fiona" <[email protected]> wrote in
>>message
>>news:[email protected]...
>>> Received advise previously on this (thanks). However,
I
>>> need to look up entries in another separate worksheet
>>and
>>> it won't work. I'm using:
>>>
>>> =VLOOKUP('Entry 1 - New Clients'!A2,'Entry 1 - New
>>> Clients'!A2:O20,2,FALSE). It returns the persons name
>>> (great) but I need to transfer across other details
too.
>>>
>>> Basically I'm summarising one worksheet onto a
specified
>>> format in another. In addition, I somehow want it to
>>only
>>> show me those clients in 'Entry 1 - New Clients' if
the
>>> corresponding column (N) has the entry of "Active".
>>>
>>> Is this possible??
>>> Fiona
>>
>>
>>..
>>
>>
>>.
>>
>.
>

Legendary - works perfectly (apart from the enormous
statement this ends up being)

F
>-----Original Message-----
>Hi!
>
>You need an entirely different formula because you're
>doing a multiple criteria lookup.
>
>Man, I hate long sheet names! <g>
>
>It looks to me as though you want to lookup a clients
name
>in column A then lookup "active" in column N and return
>the corresponding value in column B?
>
>Something like this entered with the key combo of
>CTRL,SHIFT,ENTER:
>
>=IF(ISNA(MATCH(1,(A2:A11=A2)*
(N2:N11="active"),0)),"",INDEX
>(B2:B11,MATCH(1,(A2:A11=A2)*(N2:N11="active"),0)))
>
>Just think how much longer that formula will get once
you
>put those long sheet names in! <g>
>
>Biff
>
>>-----Original Message-----
>>Received the following reply to earlier question
(below).
>>Doesn't work. Can the above two statements be combined?
>>Ie. If one cell (in another worksheet) = "Active" then
>>lookup the values from the same worksheet.
>>
>>I've been trying:
>>VLOOKUP(IF('Entry 1 - New Clients'!
N2:N31="ACTIVE",'Entry
>>1 - New Clients'!A2,'Entry 1 - New Clients'!
>>A2:O20,2,FALSE))
>>
>>Returns ugly errors. Maybe Excel doesn't like the
>>combination of both statements?
>>
>>>>>>>>>>>>>>>>
>>Yes, just re-use the formula in another cell with a
>>different column index
>>
>>=VLOOKUP('Entry 1 - New Clients'!A2,'Entry 1 - New
>>Clients'!A2:O20,3,FALSE).
>>
>>note the ,3 not ,2
>>
>>--
>>
>>HTH
>>
>>RP
>>(remove nothere from the email address if mailing
direct)
>>
>>
>>"Fiona" <[email protected]> wrote in
>>message
>>news:[email protected]...
>>> Received advise previously on this (thanks). However,
I
>>> need to look up entries in another separate worksheet
>>and
>>> it won't work. I'm using:
>>>
>>> =VLOOKUP('Entry 1 - New Clients'!A2,'Entry 1 - New
>>> Clients'!A2:O20,2,FALSE). It returns the persons name
>>> (great) but I need to transfer across other details
too.
>>>
>>> Basically I'm summarising one worksheet onto a
specified
>>> format in another. In addition, I somehow want it to
>>only
>>> show me those clients in 'Entry 1 - New Clients' if
the
>>> corresponding column (N) has the entry of "Active".
>>>
>>> Is this possible??
>>> Fiona
>>
>>
>>..
>>
>>
>>.
>>
>.
>

did you try putting the IF statement first :
=IF('Entry 1 - New Clients'!N2="ACTIVE",VLOOKUP('Entry 1 - New
Clients'!A2,'Entry 1 - New Clients'!A2:O20,3,FALSE)),"")

Is this what you were trying to achieve ?

Fadi

On Thu, 10 Mar 2005 20:55:02 -0800,
<[email protected]> wrote:

>Received the following reply to earlier question (below).
>Doesn't work. Can the above two statements be combined?
>Ie. If one cell (in another worksheet) = "Active" then
>lookup the values from the same worksheet.
>
>I've been trying:
>VLOOKUP(IF('Entry 1 - New Clients'!N2:N31="ACTIVE",'Entry
>1 - New Clients'!A2,'Entry 1 - New Clients'!
>A2:O20,2,FALSE))
>
>Returns ugly errors. Maybe Excel doesn't like the
>combination of both statements?
>
>>>>>>>>>>>>>>>
>Yes, just re-use the formula in another cell with a
>different column index
>
>=VLOOKUP('Entry 1 - New Clients'!A2,'Entry 1 - New
>Clients'!A2:O20,3,FALSE).
>
>note the ,3 not ,2