Compare multiple cells and return other value
Solved/Closed
Related:
- Compare multiple cells in excel and return a value
- Number to words in excel - Guide
- Beyond compare - Download - File management
- If cell contains date then return value ✓ - Excel Forum
- Excel formula to check if cell contains a date - Excel Forum
- Gif in excel - Guide
4 responses
rizvisa1
Posts
4478
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
766
Jun 8, 2010 at 04:48 PM
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","")))))))
=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
BR,
Vince
rizvisa1
Posts
4478
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
766
Jun 11, 2010 at 01:17 PM
Jun 11, 2010 at 01:17 PM
Change "NoMatch1" etc to 0
