Forum Discussion

Troy Stark's avatar
Troy Stark
Copper Contributor
May 16, 2018

Need Formula: IF (CELL1 or CELL2) = (CELL3 or CELL 4) then TRUE.

Hello! I'm just getting my feet wet with formulas in Excel and I think I need a nested IF/OR formula.

 

Basically I want to compare two sets of cells. If there are any matches between the cells, I want the formula to return true. If no equivalence, then false.

 

So:

If CELL1 or CELL2 = CELL 3 or CELL4, then true. If not, false.

 

I figured out how to do this for a single cell: IF(OR(CELL1=CELL3,CELL1=CELL4),TRUE,FALSE), but can't figure out how to nest another cross-check??? Is there an easier way?


Thanks in advance!

  • Haytham Amairah's avatar
    Haytham Amairah
    Silver Contributor

    Hi Troy,

     

    You can expand the OR function with two more logical tests as follow:

    =IF(OR(A1=A3, A1=A4, A2=A3, A2=A4),TRUE,FALSE)

     

    Update:

    You don't have to nest another IF as you did in your reply above.

     

    I hope this helps you

    Regards

    • Troy Stark's avatar
      Troy Stark
      Copper Contributor

      Thank you! Your formula is much less complicated. Cheers!

  • Hi, to all!

     

    Another options could be:

     

    =OR(A1=A2, A1=A3, A2=A3, A3=A4)

    =SUMPRODUCT(COUNTIF(A1:A2,A3:A4))>0

    =OR(A1:A2=TRANSPOSE(A3:A4)) <-- Array Formula (Ctrl + Shift + Enter)

     

    Blessings!

  • Troy Stark's avatar
    Troy Stark
    Copper Contributor

    I figured it out:

     

    =IF(OR(CELL1=CELL3,CELL1=CELL4),TRUE,IF(OR(CELL2=CELL3,CELL2=CELL4),TRUE))

Resources