Results 1 to 5 of 5

Thread: index/match formula? comparing 4 columns
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Mar 2006
    Posts
    123
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default index/match formula? comparing 4 columns

    hi,

    so the workbook is set up like this, with 4 columns:

    Column A - country code
    Column B - country name for that code

    and then you have the same, but not in the same order
    Column C - country code
    Column D - country name

    i need a formula looks at the country code in C2. If C2 is present in column A, formula then checks to see if the respective country name, in D2, is also what is in column B for that particular country code.

    is that clear, or more clarification needed?

    thanks in advance

  2. #2
    MrExcel MVP
    Join Date
    Aug 2010
    Location
    Rio de Janeiro BRAZIL
    Posts
    15,904
    Post Thanks / Like
    Mentioned
    17 Post(s)
    Tagged
    8 Thread(s)

    Default Re: index/match formula? comparing 4 columns

    Maybe...

    =IF(COUNTIFS(A:A,C2,B:B,D2),"Found","Not found")

    M.

  3. #3
    Board Regular DanteAmor's Avatar
    Join Date
    Dec 2018
    Location
    México
    Posts
    3,917
    Post Thanks / Like
    Mentioned
    37 Post(s)
    Tagged
    10 Thread(s)

    Default Re: index/match formula? comparing 4 columns

    Try this

     ABCDE
    1COUNTRY CODECOUNTRY NAMECOUNTRY CODECOUNTRY NAMERESULT
    212345BRAZIL245FRANCECode and Name match
    3245FRANCE4455CANADADont exists code
    47899EGYPT12345ENGLANDCode exists but differet name

    CellFormula
    E2=IFERROR(IF(VLOOKUP(C2,$A$2:$B$4,2,0)=D2,"Code and Name match","Code exists but differet name"),"Dont exists code")
    Regards Dante Amor

  4. #4
    Board Regular
    Join Date
    Mar 2006
    Posts
    123
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: index/match formula? comparing 4 columns

    this is perfect. but now I realize I need to have this displayed in another way...not sure I can do it with formula.

    Thanks for this though, gonna use it another time!

  5. #5
    Board Regular DanteAmor's Avatar
    Join Date
    Dec 2018
    Location
    México
    Posts
    3,917
    Post Thanks / Like
    Mentioned
    37 Post(s)
    Tagged
    10 Thread(s)

    Default Re: index/match formula? comparing 4 columns

    Quote Originally Posted by rishijain11 View Post
    this is perfect. but now I realize I need to have this displayed in another way...not sure I can do it with formula.

    Thanks for this though, gonna use it another time!
    That's right, at no time did you mention how you wanted the result, so the proposals may not be what you had in mind.
    But if you put what you need, maybe ...
    Regards Dante Amor

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •