1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

    Yours,
    Chandoo
  2. 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...

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

Discussion in 'Ask an Excel Question' started by LisaAnn, Jan 9, 2019.

  1. LisaAnn

    LisaAnn New Member

    Messages:
    9
    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!!

    Attached Files:

  2. bosco_yip

    bosco_yip Excel Ninja

    Messages:
    2,187
    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: Jan 9, 2019
  3. LisaAnn

    LisaAnn New Member

    Messages:
    9
    This TOTALLY WORKED on my real-life data @ work! Y'all ROCK! THANK YOU!!!!
  4. Peter Bartholomew

    Peter Bartholomew Well-Known Member

    Messages:
    705
    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 like this.

Share This Page