+ Reply to Thread
Results 1 to 5 of 5

MATCH or VLOOKUP multiple columns with text

  1. #1
    Registered User
    Join Date
    06-04-2012
    Location
    Ohio
    MS-Off Ver
    MS Office 2003, 2007, 2010
    Posts
    14

    MATCH or VLOOKUP multiple columns with text

    Thanks for any help in advance.

    My issue is this:

    I have multiple columns with names. I want to cross reference each of these cells (names) with all other cells to see if they occur more than once.
    For instance, I want a formula that will look in the range specified and return to me the cell address or addresses of all occurrences. The below example would return "A2, C3" for occurences of "Bob, A" not counting the first occurence.

    A B C
    1 Bob, A Fred, D Alex, T

    2 Bob, A Stacy, J Alicia, S

    3 Man, B Ted, B Bob, A

    I have tried using =ADDRESS(MATCH("ABC",A:A,0),2) for example to no avail. I am running into the problem that the match function only works on one column at a time. I have 20 columns of names. If you are wondering why, they are authors of scientific papers and are listed in order of 1st author to last.

    Thanks again!

  2. #2
    Registered User
    Join Date
    06-04-2012
    Location
    Ohio
    MS-Off Ver
    MS Office 2003, 2007, 2010
    Posts
    14

    Re: MATCH or VLOOKUP multiple columns with text

    Bump bump bump

  3. #3
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,926

    Re: MATCH or VLOOKUP multiple columns with text

    I guess its too much to hope that you have a list of all the names? If you do, you could do a countif() to at least find out how many times a particular name appears. Other than a series of (a whole lot of) helper columns, I cant think of a regular formula way of doing what you want Maybe some-one can come up with a VBA solution for you
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  4. #4
    Registered User
    Join Date
    06-04-2012
    Location
    Ohio
    MS-Off Ver
    MS Office 2003, 2007, 2010
    Posts
    14

    Re: MATCH or VLOOKUP multiple columns with text

    Do you mean a concatenation of all names in one column? I can do that. Attached is a sample of my data. The countif() might come in handy later so thanks for that!
    Attached Files Attached Files

  5. #5
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,926

    Re: MATCH or VLOOKUP multiple columns with text

    this may be a start for you, it will extract the unique names from each column. in your sample, put this in F2, and copy down and across...
    =iferror(INDEX(A$1:A$200,MATCH(0,INDEX(COUNTIF(F$1:F1,A$1:A$200),0,0),0)),"")

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Tags for this Thread

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