ExcelBanter - VLOOKUP + IF STATEMENTS

ExcelBanter (https://www.excelbanter.com/)

- Excel Discussion (Misc queries) (https://www.excelbanter.com/excel-discussion-misc-queries/)

- - VLOOKUP + IF STATEMENTS (https://www.excelbanter.com/excel-discussion-misc-queries/17158-vlookup-if-statements.html)

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" wrote in
message
...
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

..

Biff

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" wrote in
message
...
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

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" wrote in
message
...
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

Biff

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" wrote in
message
...
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
.

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" wrote in
message
...
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

..

.

.

Fiona

Fadi Chalouhi

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

All times are GMT +1. The time now is 04:51 PM.