Previous Topic Next Topic

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

Answer 1 / 4
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
 768
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","")))))))
Answer 2 / 4

Sorry, there is one more thing. If any of the tests do not give results, the answer should be "0".

BR,
Vince
Answer 3 / 4
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
 768
Change "NoMatch1" etc to 0
Answer 4 / 4

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
Ask a question