# Compare multiple cells and return other value

Solved/Closed
Related:

- Compare multiple cells in excel and return a value
- If cell contains date then return value ✓ - Excel Forum
- Excel if range of cells contains specific text then return value ✓ - Excel Forum
- Excel "IF" function w/ date in test cell ✓ - Excel Forum
- Number to words in excel - Guide
- Beyond compare - Download - File management

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