If AND formula

W

Wanna Learn

Hello

Here is the formula
=if(and(Vlookup(U5,'[Daily Export .xls]Raw'!$A$1:$D$123,and,('[Daily
Export.xls]Raw'!$C$1:$C$123)="FAX" ,4, false))))

this is what I want to do
If the name appears in column U lookup in workbook 2 AND if column C has
the word FAX then answer is in column D.(also need to add and if is error
“0†)

I did not even try to add the "if error" to the formula because the one
above does not work
 
P

PCLIVE

What exactly are you looking up? Is this a double-lookup?

Ex. Are you trying to do a Vlookup on [Daily Export .xls]Raw'!$A$1:$D$123
for the value in U5....AND a Vlookup on '[Daily
Export.xls]Raw'!$C$1:$C$123 for the word "FAX"?

Let's break it down this way:
If the name in U5 appears anywhere in the specified range of A1:A123, then
what do you want to do? Do you then want to see if the value in the
adjacent cell in column C is "FAX"?
And if both of those are true, then do you want to return the value in the
same row of column D?

If that is the case, what types of values are in column D. Are they
numbers? Will there only be one row that matches the two criteria, or could
there be more than one row? And if that is the case, then what should
happen.

Regards,
Paul
 
W

Wanna Learn

Thanks Paul
Here we go . Yes to the Let's break it down paragraph . Values in column
D are numbers . And , Yes in some cases word fax may appear more than once
with the same person's name . In that case I need to add the 2 fax numbers
to make one total for the person.
example
Daily Export report has the following
Column a = name of person
Column b = id number of person
Column c = type of transaction such as phone, fax, e mail etc
Column d = a number e.g. 25
The thing is that the person’s name appears more than once. And that’s OK
Example
Name ID Type No.
Jones 111 FAX 25
Jones 111 Phone 30
Jones 111 e mails 50
Jones 111 Fax 1

What I want to do is a vlookup the name in column u to the daily export
report range A1:D123 and if column c reads fax then the total in column D
but if is error = 0
thank you thank you
PCLIVE said:
What exactly are you looking up? Is this a double-lookup?

Ex. Are you trying to do a Vlookup on [Daily Export .xls]Raw'!$A$1:$D$123
for the value in U5....AND a Vlookup on '[Daily
Export.xls]Raw'!$C$1:$C$123 for the word "FAX"?

Let's break it down this way:
If the name in U5 appears anywhere in the specified range of A1:A123, then
what do you want to do? Do you then want to see if the value in the
adjacent cell in column C is "FAX"?
And if both of those are true, then do you want to return the value in the
same row of column D?

If that is the case, what types of values are in column D. Are they
numbers? Will there only be one row that matches the two criteria, or could
there be more than one row? And if that is the case, then what should
happen.

Regards,
Paul


--

Wanna Learn said:
Hello

Here is the formula
=if(and(Vlookup(U5,'[Daily Export .xls]Raw'!$A$1:$D$123,and,('[Daily
Export.xls]Raw'!$C$1:$C$123)="FAX" ,4, false))))

this is what I want to do
If the name appears in column U lookup in workbook 2 AND if column C
has
the word FAX then answer is in column D.(also need to add and if is
error
"0" )

I did not even try to add the "if error" to the formula because the one
above does not work
 
P

PCLIVE

See if this does what you want.

=SUMPRODUCT(--('[Daily Export.xls]Raw'!$A$1:$A$123=U5),--('[Daily
Export.xls]Raw'!$C$1:$C$123="FAX"),'[Daily Export.xls]Raw'!$D$1:$D$123)

HTH,
Paul

--

Wanna Learn said:
Thanks Paul
Here we go . Yes to the Let's break it down paragraph . Values in
column
D are numbers . And , Yes in some cases word fax may appear more than
once
with the same person's name . In that case I need to add the 2 fax numbers
to make one total for the person.
example
Daily Export report has the following
Column a = name of person
Column b = id number of person
Column c = type of transaction such as phone, fax, e mail etc
Column d = a number e.g. 25
The thing is that the person's name appears more than once. And that's OK
Example
Name ID Type No.
Jones 111 FAX 25
Jones 111 Phone 30
Jones 111 e mails 50
Jones 111 Fax 1

What I want to do is a vlookup the name in column u to the daily export
report range A1:D123 and if column c reads fax then the total in column
D
but if is error = 0
thank you thank you
PCLIVE said:
What exactly are you looking up? Is this a double-lookup?

Ex. Are you trying to do a Vlookup on [Daily Export
.xls]Raw'!$A$1:$D$123
for the value in U5....AND a Vlookup on '[Daily
Export.xls]Raw'!$C$1:$C$123 for the word "FAX"?

Let's break it down this way:
If the name in U5 appears anywhere in the specified range of A1:A123,
then
what do you want to do? Do you then want to see if the value in the
adjacent cell in column C is "FAX"?
And if both of those are true, then do you want to return the value in
the
same row of column D?

If that is the case, what types of values are in column D. Are they
numbers? Will there only be one row that matches the two criteria, or
could
there be more than one row? And if that is the case, then what should
happen.

Regards,
Paul


--

Wanna Learn said:
Hello

Here is the formula
=if(and(Vlookup(U5,'[Daily Export .xls]Raw'!$A$1:$D$123,and,('[Daily
Export.xls]Raw'!$C$1:$C$123)="FAX" ,4, false))))

this is what I want to do
If the name appears in column U lookup in workbook 2 AND if column C
has
the word FAX then answer is in column D.(also need to add and if is
error
"0" )

I did not even try to add the "if error" to the formula because the one
above does not work
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top