• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Matching 3 Columns with all permutation And Combination

Hii

Please help me out to define the formula to compare the values in three columns with eachother. Sample data looks like as under

Sno A B C Result
1 1000 1000 1000 All values are equal
2 1000 1500 2000 No Values are matching with eachother
3 1000 1000 1500 Value A & B are matching but both are not equal to C
4 1000 1500 1000 Value A & C are matching but both are not equal to B
5 1000 1500 1500 Value B & C are matching but both are not equal to A

I find above combinations.

Regards
 
A long nested if can do it, but is it perhaps not the most elegant.
=IF(AND(B2=C2;B2=D2),"All match",IF(AND(B2=C2;B2<>D2),"A & B Match",IF(AND(B2=D2;B2<>C2),"A and C Match",IF(AND(B2<>C2;C2=D2),"B and C Match","No Matching values"))))
 
Or a "binary" approach
=VLOOKUP(CONCATENATE(--(B2=C2),--(B2=D2),--(C2=D2)),{"111"\"All match","000"\"No Matching values","100"\"A & B Match","010"\"A and C Match","001"\"B and C Match"},2,FALSE)
 
Similar to GraH - Guido's idea, but using a BIN2DEC() and a Lookup result list

E2, copied down :

=INDEX(G$2:G$6,MATCH(BIN2DEC(0+(B2=C2)&0+(C2=D2)&0+(B2=D2))+1,{1,2,3,5,8},0))

Regards
Bosco
 

Attachments

  • Matchin3columns(1).xlsx
    13 KB · Views: 14
Last edited:
Back
Top