+ Reply to Thread
Results 1 to 4 of 4

Issue using Index/Match to pull multiple occurrences of "Match" criteria

  1. #1
    Registered User
    Join Date
    01-09-2014
    Location
    New York, United States
    MS-Off Ver
    Excel 2008
    Posts
    3

    Issue using Index/Match to pull multiple occurrences of "Match" criteria

    Hi

    I am attempting to use the Index/Match to match each instance of "Person Responsible" that is mentioned in Column D (D:D) (All tab), which have been pulled from the 'All Tab', and pull subsequent column names to create individualized lists on other tabs. The issue is that the Index/Match formula is only matching the 1st instance of the "Name" and not moving on to the subsequent matches in the search array.

    Is a ROW function needed to help determine when to move on? I'm modestly proficient with excel and I'm sure it's not that hard, but any help would be greatly appreciated.

    I have attached a sample spreadsheet for any possible assistance.

    Thank you in advance.
    Attached Files Attached Files

  2. #2
    Forum Guru benishiryo's Avatar
    Join Date
    03-25-2011
    Location
    Singapore
    MS-Off Ver
    Excel 2013
    Posts
    5,136

    Re: Issue using Index/Match to pull multiple occurrences of "Match" criteria

    welcome to the forum. you would have to use an array formula in A2:
    =IFERROR(INDEX(All!A$2:A$10,SMALL(IF(All!$D$2:$D$10="employee 1",ROW(All!$D$2:$D$10)),ROWS(A$2:A2))-ROW(All!$D$2)+1),"")

    ...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.

    note that this formula can cause the workbook to calculate much slower, so go easy on the range (don't use whole column). to prevent zeroes from appearing (those are blanks in Worksheet All), format cells to the Custom with the format you want + 2 semi colons. for eg. All columns except E can be:
    General;;

    Column E would be something like:
    d/m/yyyy;;

    Thanks, if you have clicked on the * and added our rep.

    If you're satisfied with the answer, click Thread Tools above your first post, select "Mark your thread as Solved".

    "Contentment is not the fulfillment of what you want, but the realization of what you already have."


    Tips & Tutorials I Compiled | How to Get Quick & Good Answers

  3. #3
    Registered User
    Join Date
    01-09-2014
    Location
    New York, United States
    MS-Off Ver
    Excel 2008
    Posts
    3

    Re: Issue using Index/Match to pull multiple occurrences of "Match" criteria

    Thank you for the quick response. Couple of q's. I'm on a mac, so not sure of equivalent ctrl shift enter command, though I've tried several different combos of keys.
    also, this formula works on the first line I paste it in but not on the second line.

    thanks also for additional info on other column formatting.

  4. #4
    Registered User
    Join Date
    01-09-2014
    Location
    New York, United States
    MS-Off Ver
    Excel 2008
    Posts
    3

    Re: Issue using Index/Match to pull multiple occurrences of "Match" criteria

    saw a notice but popup was blocked by my browser, I've disabled. please try again. thanks.

+ 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: 4
    Last Post: 11-17-2013, 12:05 PM
  2. [SOLVED] Multiple criteria lookup - First match :exact" and second match "Closest"
    By mweichorn in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 07-16-2013, 12:14 PM
  3. Replies: 7
    Last Post: 04-11-2013, 12:46 AM
  4. Replies: 10
    Last Post: 12-18-2012, 07:59 AM
  5. Replies: 0
    Last Post: 07-09-2009, 04:07 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