+ Reply to Thread
Results 1 to 6 of 6

Column from matching values

  1. #1
    Registered User
    Join Date
    01-29-2019
    Location
    Dublin, Ireland
    MS-Off Ver
    16.7
    Posts
    40

    Column from matching values

    Hi,

    I have a two columns of names, different length, and want to come up with a third column which lists all the matching matching values of columns 1 and 2. Any idea of how to do that?

  2. #2
    Forum Expert
    Join Date
    03-23-2004
    Location
    London, England
    MS-Off Ver
    Excel 2019
    Posts
    7,062

    Re: Column from matching values

    You havent stated what you expect the output to look like and I can see several ways of outputting this.

    Post a sample spreadsheet with expected results, remove any sensitive data, create a mockup example if necessary.
    Use the Go Advanced option at the bottom of the page then scroll down to Manage Attachments as the "paperclip" method does not work on this forum.
    Regards
    Special-K

    Ensure you describe your problem clearly, I have little time available to solve these problems and do not appreciate numerous changes to them.

  3. #3
    Registered User
    Join Date
    01-29-2019
    Location
    Dublin, Ireland
    MS-Off Ver
    16.7
    Posts
    40

    Re: Column from matching values

    Capture.PNG


    Just like that

  4. #4
    Forum Expert
    Join Date
    03-23-2004
    Location
    London, England
    MS-Off Ver
    Excel 2019
    Posts
    7,062

    Re: Column from matching values

    I cant get this working without a helper column
    Your blank top row and column implies the data starts at B3 (Billy).

    in D3
    =IFERROR(VLOOKUP(B3,C$3:C$6,1,0),"")

    in E3
    =IFERROR(INDEX($D$3:$D$8,AGGREGATE(15,6,ROW($D$3:$D$8)/(($D$3:$D$8<>"")),ROWS(A$3:A3))-(3-1),1),"")

    This assumes the first column will be equal to or longer than the second column, if that's not the case you'll need to swap the B and C columns around in the formulas

  5. #5
    Registered User
    Join Date
    01-29-2019
    Location
    Dublin, Ireland
    MS-Off Ver
    16.7
    Posts
    40

    Re: Column from matching values

    Thanks, very helpful.

  6. #6
    Forum Expert
    Join Date
    09-25-2015
    Location
    Milan Italy
    MS-Off Ver
    office 365
    Posts
    1,776

    Re: Column from matching values

    A
    B
    C
    D
    E
    1
    a b c
    2
    Billy Martin Jhon
    3
    Jim Charles Martin
    4
    Jhon Jhon
    5
    Alfred
    6
    Jack
    7
    Martin


    E2=IFERROR(INDEX($A$2:$A$100,AGGREGATE(15,6,ROW($A$2:$A$100)-ROW($A$2)+1/(ISNUMBER(MATCH($A$2:$A$100,$C$2:$C$100,0))),ROWS($A$2:A2))),"")

    copy down

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Replies: 3
    Last Post: 04-22-2019, 05:04 PM
  2. Replies: 8
    Last Post: 07-04-2018, 10:03 AM
  3. [SOLVED] Fill up a column in one work sheet by matching column values in 2 worksheets
    By marimuthuravi in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 06-11-2018, 11:38 PM
  4. If column range contains specified column, concatenate matching values
    By johndon in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 10-14-2016, 04:40 PM
  5. Replies: 5
    Last Post: 08-01-2014, 04:31 AM
  6. Sum Values in a Table Matching Specific Row and Column Values
    By dubasteins in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 07-15-2014, 09:06 AM
  7. Matching column values in 2 sheets and copying another column over if match
    By cuberis in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 08-20-2012, 06:55 PM

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