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

1. ## 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.

2. ## 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;;

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. ## 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.

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

#### 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