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

Vince

## 4 replies

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","")))))))
Sorry, there is one more thing. If any of the tests do not give results, the answer should be "0".

BR,
Vince
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
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