Previous Topic Next Topic

Compare multiple cells and return other value

Solved/Closed
Vince - Jun 8, 2010 at 03:57 PM
 Vince - Jun 11, 2010 at 01:44 PM
Hello,

a problematic case, when comparing values in multiple cells (rows) in Excel.

In a single cell , "A1" I would need an answer depending on the following test:
- if B1=E1orF1orG1 and C1=E1orF1orG1 and D1=E1orF1orG1, the answer is X
- if B1=E1orF1orG1 and C1=E1orF1orG1 and (D1 is not E1orF1orG1), the answer is Y
- if B1=E1orF1orG1 and (C1 is not E1orF1orG1) and D1=E1orF1orG1, the answer is Y
- if (B1 is not E1orF1orG1) and C1=E1orF1orG1 and D1=E1orF1orG1, the answer is Y
- if B1=E1orF1orG1 and (C1 is not E1orF1orG1) and (D1 is not E1orF1orG1), the answer is Z
- if (B1 is not E1orF1orG1) and (C1 is not E1orF1orG1) and D1=E1orF1orG1, the answer is Z
- if (B1 is not E1orF1orG1) and C1=E1orF1orG1 and (D1 is not E1orF1orG1), the answer is Z

Can you help me with this one?

Thanks in advance!

Vince


4 replies

Answer 1 / 4
rizvisa1
Posts
4479
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
 770
Jun 8, 2010 at 04:48 PM
Try this

=IF(AND(OR(B1=E1,B1=F1,B1=G1),OR(C1=E1,C1=F1,C1=G1)),IF(OR(D1=E1,D1=F1,D1=G1),"X",IF(AND(D1<>E1,D1<>F1,D1<>G1),Y,"NoMatch1")),IF(AND(OR(B1=E1,B1=F1,B1=G1),AND(C1<>E1,C1<>F1,C1<>G1)),IF(OR(D1=E1,D1=F1,D1=G1),"Y",IF(AND(D1<>E1,D1<>F1,D1<>G1),Z,"NoMatch2")),IF(AND(AND(B1<>E1,B1<>F1,B1<>G1),OR(C1=E1,C1=F1,C1=G1)),IF(OR(D1=E1,D1=F1,D1=G1),"Y",IF(AND(D1<>E1,D1<>F1,D1<>G1),Z,"NoMatch3")),IF(AND(AND(B1<>E1,B1<>F1,B1<>G1),AND(C1<>E1,C1<>F1,C1<>G1),OR(D1=E1,D1=F1,D1=G1)),"Z","NoMatch4"))))

Thats pushing the envelope though. You might be better off with a custom defined function. Easy to maintain and easy of expand. My initial If statement was beyond xl limitation. In case you are curious what was that
=IF(AND(OR(B1=E1,B1=F1,B1=G1),OR(C1=E1,C1=F1,C1=G1),OR(D1=E1,D1=F1,D1=G1)),"X",IF(AND(OR(B1=E1,B1=F1,B1=G1),OR(C1=E1,C1=F1,C1=G1),AND(D1<>E1,D1<>F1,D1<>G1)),"Y",IF(AND(OR(B1=E1,B1=F1,B1=G1),AND(C1<>E1,C1<>F1,C1<>G1),OR(D1=E1,D1=F1,D1=G1)),"Y",IF(AND(AND(B1<>E1,B1<>F1,B1<>G1),OR(C1=E1,C1=F1,C1=G1),OR(D1=E1,D1=F1,D1=G1)),"Y",IF(AND(OR(B1=E1,B1=F1,B1=G1),AND(C1<>E1,C1<>F1,C1<>G1),AND(D1<>E1,D1<>F1,D1<>G1)),"Z",IF(AND(AND(B1<>E1,B1<>F1,B1<>G1),AND(C1<>E1,C1<>F1,C1<>G1),OR(D1=E1,D1=F1,D1=G1)),"Z",IF(AND(AND(B1<>E1,B1<>F1,B1<>G1),OR(C1=E1,C1=F1,C1=G1),AND(D1<>E1,D1<>F1,D1<>G1)),"Z","")))))))
0
Answer 2 / 4
Vince
Jun 11, 2010 at 01:15 PM
Sorry, there is one more thing. If any of the tests do not give results, the answer should be "0".

BR,
Vince
0
Answer 3 / 4
rizvisa1
Posts
4479
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
 770
Jun 11, 2010 at 01:17 PM
Change "NoMatch1" etc to 0
0
Answer 4 / 4
Vince
Jun 11, 2010 at 01:44 PM
I'm using the longer formula, which by the way works fine and is very versatile, can it be edited? Thanks for all your help, the formulas are excellent!

V
0