+ Reply to Thread
Results 1 to 3 of 3

I need to Return Multiple Matches While Index(Match) only returns the First one.

  1. #1
    Registered User
    Join Date
    08-07-2012
    Location
    NY
    MS-Off Ver
    Excel 2007
    Posts
    9

    I need to Return Multiple Matches While Index(Match) only returns the First one.

    Hi,

    I received tremendous help on my first question, but nobody replied to my second question. I'm not sure what made my first post more attractive, but hopefully this one is as well.

    I have unique (job) names in Column A and column B has the names of different salespeople (there are 20 salespeople in total). I would like to have a formula that looks at Column A, and based on the salesperson's name (Which I wrote in Cell K1 in this case), returns the Job Name from Column A.

    I tried using the following formula which seems to work:

    =INDEX($A$1:$A$4,MATCH(K1,$B$1:$B$4,FALSE),1), but this only gives me the First

    The only problem with this formula is that only the first Job Name comes up but each SalesPerson has multiple jobs.

    Thank you very much in advance for your help.

    Regards,

    Michael W.

  2. #2
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,902

    Re: I need to Return Multiple Matches While Index(Match) only returns the First one.

    Possibly?

    =IFERROR(INDEX($A$1:$A$4,SMALL(IF($B$1:$B$4=K$1,ROW($A$1:$A$4)-ROW($A$1)+1),ROWS($A$1:$A1)),1),"")

    this needs to be confirmed with CTRL+SHIFT+ENTER not just ENTER, then copied down 4 rows.
    Where there is a will there are many ways.

    If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner

    Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.

  3. #3
    Registered User
    Join Date
    08-07-2012
    Location
    NY
    MS-Off Ver
    Excel 2007
    Posts
    9

    Re: I need to Return Multiple Matches While Index(Match) only returns the First one.

    Thank you for your reply.

    This is the tweak I did to your answer and it gets me what I want.

    My Salesrep (whose jobs I want to list are in column AW.

    The jobs are listed in Column 1 (which is indicated by the very last "1" in the formula.

    A39 is where I am writing the name of the sales rep that I want searched for.

    I am not sure exactly how the "Row" formula works.

    =INDEX($A$1:$AW$17,SMALL(IF($AW$1:$AW$17=$A$39,ROW($AW$1:$AW$17)),ROW(1:1)),1)

    Regards...

+ 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