VLOOKUP + IF STATEMENTS

G

Guest

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)


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
Click to expand...


..
 
B

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
 
B

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.
Click to expand...

That's a good idea!

Biff
 
F

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
 
Top