+ Reply to Thread
Results 1 to 5 of 5

Excel 2007 : Need to Compare three columns for matching values and populate a predetermined Value.

  1. #1
    Registered User
    Join Date
    03-20-2012
    Location
    KY, USA
    MS-Off Ver
    Excel 2007
    Posts
    2

    Need to Compare three columns for matching values and populate a predetermined Value.

    I have three columns A, B and C and need to compare the text values in each cell in column A exists/matches to any of the cells in column B and column C. If it matches to column B and C I need to populate a value 2 in a new column D else I need to populate a value 1 in column D if it matches to only one of the column and a value 0 if it does not have a match in either of the columns. I tried using a vlook up and a match condition but to no avail. Any help on this regard would be highly appreciated.

  2. #2
    Forum Guru tigeravatar's Avatar
    Join Date
    03-25-2011
    Location
    Colorado, USA
    MS-Off Ver
    Excel 2003 - 2013
    Posts
    5,361

    Re: Need to Compare three columns for matching values and populate a predetermined Value.

    jinsi.george,

    Welcome to the forum!
    In cell D1 and copied down:
    =CHOOSE((OR(COUNTIF(B:B,A1))+OR(COUNTIF(C:C,A1))*2)+1,0,1,1,2)

    Same formula, simplified:
    =OR(COUNTIF(B:B,A1))+OR(COUNTIF(C:C,A1))
    Last edited by tigeravatar; 03-20-2012 at 01:16 PM.
    Hope that helps,
    ~tigeravatar

    Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble

  3. #3
    Valued Forum Contributor
    Join Date
    03-16-2012
    Location
    Aarhus, Denmark
    MS-Off Ver
    Excel 2007
    Posts
    992

    Re: Need to Compare three columns for matching values and populate a predetermined Value.

    If column A values can only match ones in each of columns B and C, then you can use =Sumproduct((A1=B:B)*1;(A1=C:C)*1) and copy down.
    Sincerely
    Søren Larsen

    "Give a man a fish, and you'll feed him for a day. Give a man a fishing rod, and he'll steal your yacht!"

  4. #4
    Registered User
    Join Date
    03-20-2012
    Location
    KY, USA
    MS-Off Ver
    Excel 2007
    Posts
    2

    Re: Need to Compare three columns for matching values and populate a predetermined Value.

    Quote Originally Posted by tigeravatar View Post
    jinsi.george,

    Welcome to the forum!
    In cell D1 and copied down:
    =CHOOSE((OR(COUNTIF(B:B,A1))+OR(COUNTIF(C:C,A1))*2)+1,0,1,1,2)

    Same formula, simplified:
    =OR(COUNTIF(B:B,A1))+OR(COUNTIF(C:C,A1))
    __________________________________________________________________

    Thanks for the solution. Though I have one more concern. If I want to depict these predetermined values with a text value For eg. matching to Column B would show up as B, matching to column C would need to show up as C and matching to both as D. what options could I use?

  5. #5
    Forum Guru tigeravatar's Avatar
    Join Date
    03-25-2011
    Location
    Colorado, USA
    MS-Off Ver
    Excel 2003 - 2013
    Posts
    5,361

    Re: Need to Compare three columns for matching values and populate a predetermined Value.

    jinsi.george,

    Use the first formula I posted, except change the last part of it to what you want to display:
    =CHOOSE((OR(COUNTIF(B:B,A1))+OR(COUNTIF(C:C,A1))*2)+1,"Neither","B","C","D")

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1