Thanks Thanks:  0
Likes Likes:  0
Page 1 of 8 123 ... LastLast
Results 1 to 10 of 77

Thread: Index Match data to provide multiple results in columns

  1. #1
    New Member
    Join Date
    May 2014
    Posts
    7
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Index Match data to provide multiple results in columns

    I need some help with a spreadsheet that contains 2 worksheets.

    The first sheet contains part numbers and the assemblies that they are used in. Each part number can be used in many assemblies.

    On the 2nd sheet is a list of just the part numbers without duplicates. The aim is to create columns to the right of each part number that identifies all the assemblies that the part is used in.

    I have looked at this link How To Return Multiple Match Values in Excel Using INDEX-MATCH or VLOOKUP | eImagine Technology Group but I do not want the results to be down a column but to go across the row.

    Any help appreciated

  2. #2
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    84,270
    Post Thanks / Like
    Mentioned
    39 Post(s)
    Tagged
    8 Thread(s)

    Default Re: Index Match data to provide multiple results in columns

    This is how non-robust formula set ups propagate.

    Care to post a small (5 row) sample from the source sheet?
    Assuming too much and qualifying too much are two faces of the same problem.

  3. #3
    New Member
    Join Date
    May 2014
    Posts
    7
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Index Match data to provide multiple results in columns

    Using the example I linked to in my post, the data and results should look like this:

    Part No Assembly Lookup Match 1 Match 2
    Duck Daffy Duck Daffy
    Mouse Mickey Mouse Mickey Minnie
    Mouse Minnie Bunny Bugs
    Bunny Bugs Pig Porky
    Pig Porky Dog Goofy Pluto
    Dog Goofy
    Dog Pluto
    Last edited by lomond44; Sep 23rd, 2014 at 12:12 AM.

  4. #4
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    84,270
    Post Thanks / Like
    Mentioned
    39 Post(s)
    Tagged
    8 Thread(s)

    Default Re: Index Match data to provide multiple results in columns

    E2, control+shift+enter, not just enter, copy acrossas far as needed, and down:
    Code:
    =IFERROR(INDEX($B$2:$B$8,SMALL(IF($A$2:$A$8=$D2,ROW($B$2:$B$8)-ROW($D$2)+1),
      COLUMNS($E2:E2))),"")
    
    Assuming too much and qualifying too much are two faces of the same problem.

  5. #5
    New Member
    Join Date
    May 2014
    Posts
    7
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Index Match data to provide multiple results in columns

    Perfect! Thank you very much!

  6. #6
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    84,270
    Post Thanks / Like
    Mentioned
    39 Post(s)
    Tagged
    8 Thread(s)

    Default Re: Index Match data to provide multiple results in columns

    Quote Originally Posted by lomond44 View Post
    Perfect! Thank you very much!
    You are welcome.
    Assuming too much and qualifying too much are two faces of the same problem.

  7. #7
    New Member
    Join Date
    Dec 2014
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Index Match data to provide multiple results in columns

    How do you enter multiple criteria, like mouse and disney?

  8. #8
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    84,270
    Post Thanks / Like
    Mentioned
    39 Post(s)
    Tagged
    8 Thread(s)

    Default Re: Index Match data to provide multiple results in columns

    Quote Originally Posted by RebelRebel View Post
    How do you enter multiple criteria, like mouse and disney?
    Try to provide a small sample and the expected result(s)...
    Assuming too much and qualifying too much are two faces of the same problem.

  9. #9
    New Member
    Join Date
    Dec 2014
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Index Match data to provide multiple results in columns

    Quote Originally Posted by Aladin Akyurek View Post
    Try to provide a small sample and the expected result(s)...
    Origin Animal Name Lookup: Mouse, Disney
    Warner Duck Daffy
    Disney Mouse Mickey Matches: Mickey
    Disney Mouse Minnie Minnie
    Warner Bunny Bugs
    Warner Pig Porky
    Disney Dog Goofy
    Disney Dog Pluto
    20th Cent Mouse Mighty

  10. #10
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    84,270
    Post Thanks / Like
    Mentioned
    39 Post(s)
    Tagged
    8 Thread(s)

    Default Re: Index Match data to provide multiple results in columns

    Are Mouse, Disney in a single cell?
    Assuming too much and qualifying too much are two faces of the same problem.

Some videos you may like

User Tag List

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
  •