NESTED IF STATEMENT

J

jmsbert

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!
 
A

Ashish Mathur

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
 
J

jmsbert

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!
 
S

swatsp0p

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?
 
J

jmsbert

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!
 
S

swatsp0p

Are you sure you are ARRAY entering this formula (holding down CTRL an
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
 
J

jmsbert

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!
 
S

swatsp0p

jmsbert said:
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!
Click to expand...

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.
 
J

jmsbert

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