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

• 9 KB Views: 6

bosco_yip

Excel Ninja
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:

LisaAnn

New Member
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!!!!

Peter Bartholomew

Well-Known Member
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.

• Thomas Kuriakose and sureshsonti