Compare multiple cells and return other value Solved/Closed

Question

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


Configuration: Windows XP / Safari 533.4

rizvisa1 · Answer

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","")))))))

Vince · Answer

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

rizvisa1 · Answer

Change "NoMatch1" etc to 0

Vince · Answer

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

Compare multiple cells and return other value

4 responses

Similar discussions

Subscribe To Our Newsletter!