Compare multiple cells and return other value [Solved/Closed]

Report
-
 Vince -
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

Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
761
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","")))))))
Sorry, there is one more thing. If any of the tests do not give results, the answer should be "0".

BR,
Vince
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
761
Change "NoMatch1" etc to 0
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

Subscribe To Our Newsletter!

The Best of CCM in Your Inbox

Subscribe To Our Newsletter!