NESTED IF STATEMENT
 

How can I compare value in column A to column D and also compare column B to
column E? If A & D agree and B & E agree, return value of column C.

Thank you!

you dont need to use a nested if

=(and(A1=D1,B1=E1),C1, "otherwise")
"jmsbert" wrote:

How can I compare value in column A to column D and also compare column B to
column E? If A & D agree and B & E agree, return value of column C.

Thank you!

Can I compare A1 to the whole D column, not just the cell in that row?

Thanks!


"bj" wrote:

you dont need to use a nested if

=(and(A1=D1,B1=E1),C1, "otherwise")
"jmsbert" wrote:

How can I compare value in column A to column D and also compare column B to
column E? If A & D agree and B & E agree, return value of column C.

Thank you!

Hi,

On the comparison part of the question, you may array enter
(Ctrl+Shift+Enter) the following

=or(exact(cell of data in col. A,range of column D)).

Copy this formula down. The result will be TRUE wherever there is a
repitition.

You may use the same formula for comparison of data in column B & E

Regards,

Ashish

"jmsbert" wrote:

How can I compare value in column A to column D and also compare column B to
column E? If A & D agree and B & E agree, return value of column C.

Thank you!

Thank you for your suggestion. Unfortunately I still can't get this to work.

A1=10 B1=3 C1=650
A2=30B B2=10 C2=XY
A3=blank B3=10 C3=100

Since A1 has a 10 and column B has a 10 anywhere in the column, I want to
display value in C1.
Since A2 has 30B and column B does not at all, I want to display "no match"
Does it matter that column C has more rows than column A?

Thanks!


"Ashish Mathur" wrote:

Hi,

On the comparison part of the question, you may array enter
(Ctrl+Shift+Enter) the following

=or(exact(cell of data in col. A,range of column D)).

Copy this formula down. The result will be TRUE wherever there is a
repitition.

You may use the same formula for comparison of data in column B & E

Regards,

Ashish

"jmsbert" wrote:

How can I compare value in column A to column D and also compare column B to
column E? If A & D agree and B & E agree, return value of column C.

Thank you!

Try this, array entered:

{=IF(OR(EXACT(A1,$B$1:$B$3))=TRUE,C1,"no match")} and copy down your
range.

of course adjust ranges to match your data...

Does this meet your needs?


--
swatsp0p


------------------------------------------------------------------------
swatsp0p's Profile: http://www.excelforum.com/member.php...o&userid=15101
View this thread: http://www.excelforum.com/showthread...hreadid=473940

Sorry, I still keep getting no match for everything. It doesn't seem to
compare A1 to the whole B column. If I enter=OR(EXACT(A1,B:B),C1), I get
TRUE for a response, but it won't pull in value of C1. It also seems to have
a problem if columns being compared are not the same number of rows.

Thanks!

"swatsp0p" wrote:


Try this, array entered:

{=IF(OR(EXACT(A1,$B$1:$B$3))=TRUE,C1,"no match")} and copy down your
range.

of course adjust ranges to match your data...

Does this meet your needs?


--
swatsp0p


------------------------------------------------------------------------
swatsp0p's Profile: http://www.excelforum.com/member.php...o&userid=15101
View this thread: http://www.excelforum.com/showthread...hreadid=473940

Are you sure you are ARRAY entering this formula (holding down CTRL and
SHIFT while pressing ENTER)?

This formula works in my tests.

copy and paste in D1:

=IF(OR(EXACT(A1,$B$1:$B$3))=TRUE,C1,"no match")

After ARRAY ENTER (CTRL+SHIFT+ENTER) Excel will show the formula as:

{=IF(OR(EXACT(A1,$B$1:$B$3))=TRUE,C1,"no match")}

copy this down the range...

Good Luck.


--
swatsp0p


------------------------------------------------------------------------
swatsp0p's Profile: http://www.excelforum.com/member.php...o&userid=15101
View this thread: http://www.excelforum.com/showthread...hreadid=473940

This worked! Thank you very much! I'm sorry - I have never used the CTRL -
SHIFT - ENTER before. Now I need to adjust the formula to do 2 comparisons
and if both A matches something in column B and E matches something in column
F, then I want to pull from cell C1. When I replace ORwith AND, and then try
to add another comparison, I can't get it to work. What am I doing wrong?

EX: {=IF(AND(EXACT(A1,B1:B3, D1,E1:E$3))=TRUE,C1,"no match")}

Thank you!

"swatsp0p" wrote:


Are you sure you are ARRAY entering this formula (holding down CTRL and
SHIFT while pressing ENTER)?

This formula works in my tests.

copy and paste in D1:

=IF(OR(EXACT(A1,$B$1:$B$3))=TRUE,C1,"no match")

After ARRAY ENTER (CTRL+SHIFT+ENTER) Excel will show the formula as:

{=IF(OR(EXACT(A1,$B$1:$B$3))=TRUE,C1,"no match")}

copy this down the range...

Good Luck.


--
swatsp0p


------------------------------------------------------------------------
swatsp0p's Profile: http://www.excelforum.com/member.php...o&userid=15101
View this thread: http://www.excelforum.com/showthread...hreadid=473940

jmsbert Wrote:
This worked! Thank you very much! I'm sorry - I have never used the
CTRL -
SHIFT - ENTER before. Now I need to adjust the formula to do 2
comparisons
and if both A matches something in column B and E matches something in
column
F, then I want to pull from cell C1. When I replace ORwith AND, and
then try
to add another comparison, I can't get it to work. What am I doing
wrong?

EX: {=IF(AND(EXACT(A1,B1:B3, D1,E1:E$3))=TRUE,C1,"no match")}

Thank you!


Try this to compare two conditions:

=IF(AND(OR(EXACT(A1,$B$1:$B$3)=TRUE),OR(EXACT(E1,$ F$1:$F$3))=TRUE),C1,"no
match").

Good Luck.


--
swatsp0p


------------------------------------------------------------------------
swatsp0p's Profile: http://www.excelforum.com/member.php...o&userid=15101
View this thread: http://www.excelforum.com/showthread...hreadid=473940

Thank you very much for all your help! This also worked and is very helpful.
I learned alot!

"swatsp0p" wrote:


jmsbert Wrote:
This worked! Thank you very much! I'm sorry - I have never used the
CTRL -
SHIFT - ENTER before. Now I need to adjust the formula to do 2
comparisons
and if both A matches something in column B and E matches something in
column
F, then I want to pull from cell C1. When I replace ORwith AND, and
then try
to add another comparison, I can't get it to work. What am I doing
wrong?

EX: {=IF(AND(EXACT(A1,B1:B3, D1,E1:E$3))=TRUE,C1,"no match")}

Thank you!


Try this to compare two conditions:

=IF(AND(OR(EXACT(A1,$B$1:$B$3)=TRUE),OR(EXACT(E1,$ F$1:$F$3))=TRUE),C1,"no
match").

Good Luck.


--
swatsp0p


------------------------------------------------------------------------
swatsp0p's Profile: http://www.excelforum.com/member.php...o&userid=15101
View this thread: http://www.excelforum.com/showthread...hreadid=473940