• 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.

Finding a match value in 2 columns - See attached XL file

LisaAnn

New Member
Hello!
Question: What 'formula' can I use to easily find exact matches in these two columns? For simple example, COL A and COL B both contain exact match 1011
Likewise, COL A and COL B both contain exact match 1213.

I know there's a way we can incorporate easy comparisons for this by using say "Yes" when there is an exact match or "No" when there aren't.
But I don't know how to do that.
So in comparing A and B for "123", the 'match' would be "No." For "1011", the 'match' would be "yes." For "1213", the 'match' would be "yes."

Thank you!!
 

Attachments

  • Book1.xlsx
    9 KB · Views: 6
Perhaps,

1] In comparing A and B, in C1 formula copied down :

=IF(A1="","",IF(COUNTIF($B$1:$B$10,A1)>0,"Yes","No"))

2] In comparing B and A, in D1 formula copied down :

=IF(B1="","",IF(COUNTIF($A$1:$A$5,B1)>0,"Yes","No"))

Regards
Bosco
 
Last edited:
Perhaps,

1] In comparing A and B, in C1 formula copied down :

=IF(A1="","",IF(COUNTIF($B$1:$B$10,A1)>0,"Yes","No"))

2] In comparing B and A in D1 formula copied down :

=IF(B1="","",IF(COUNTIF($A$1:$A$5,B1)>0,"Yes","No"))

Regards
Bosco

This TOTALLY WORKED on my real-life data @ work! Y'all ROCK! THANK YOU!!!!
 
A couple more formulae that could be of interest:
The single cell array formula
= COUNT( IF( COUNTIFS( B, A ), A ) )
[with CSE] gives the number of matches present and
= SMALL( IF( COUNTIFS( B, A ), A ), {1;2} )
lists the first two.

Due to the symmetry of the problem
= IF( COUNTIFS( A, B ), B )
would return the matching values just as well.
 
Back
Top