# Match/Index Formula

#### Matalyn

##### New Member
I'm combining the index and match formula in excel to extract data given a specific reference. The problem however, is that my formula only finds the first listed piece of data rather than all the data that matches the reference.

For example,

I want to find ALL the projects that "Joe" is currently assigned to. The data will be pulled from "Project Sheet" which lists all the projects. The data will be pulled into the "Joe Sheet" which will list all Joe's projects. (Joe is in cell A1 of the "Joe Sheet")

My current formula is as follows:

=index('Project Sheet'!A:F,match(A1,'Project Sheet'!G:G,0))

This successfully extracts data but only for the first project that is listed in the "Project Sheet" that Joe is assigned to, rather than all of them.

How do I alter this formula to pull every single project assigned to Joe??

Thanks!!

#### Matty

##### Well-known Member
Your formula is behaving exactly as it should, though I don't understand why you have the INDEX element referencing multiple columns. If you want all values returned, a different solution is required.

A couple of questions:

1) Which range will does Joe's name reside in?
2) Which range(s) contain the data needed to be returned?

Matty

#### Matalyn

##### New Member
Project Sheet Data (Sheet #1):
Column A-F (Data which needs to be returned, which is all associated with a specific person)
Column G: List of names who are assigned the task

I'm referencing Column G to find Joe (who may be listed more than once because he will be assigned more than one task)

Joe's Sheet (Sheet #2):
This will match the same format as the Project Sheet except the data will only be Joe's task.
This is where the formula will be.

Does this help?

#### Andrew Poulsom

##### MrExcel MVP
Try:

=IFERROR(INDEX('Project Sheet'!A\$1:A\$100,SMALL(INDEX(('Project Sheet'!G\$1:G\$100=A\$1)*ROW('Project Sheet'!G\$1:G\$100),),ROWS(A\$1:A1))),"")

copied down. Avoid using entire columns.

#### Matalyn

##### New Member
That simply extracted all the data on the Project Sheet rather than those just assigned to Joe.

#### exo33

##### New Member
This would be much easier to do with a pivot table. Just create a pivot table on "Joe Sheet" with source data referencing "Project Sheet". I am assuming that you have a column that lists an owner of a project on that sheet? Just move it to "Report filter" section of the pivot table and set it to display Joe's projects only.

#### Andrew Poulsom

##### MrExcel MVP
That simply extracted all the data on the Project Sheet rather than those just assigned to Joe.
Sorry, I was in a rush. The formula should be:

=IFERROR(INDEX('Project Sheet'!A\$1:A\$100,SMALL(IF('Project Sheet'!G\$1:G\$100=A\$1,ROW('Project Sheet'!G\$1:G\$100)),ROWS(A\$1:A1))),"")

confirmed with Ctrl+Shift+Enter.

#### Matalyn

##### New Member
Works great!! Thanks

1,081,545
Messages
5,359,438
Members
400,528
Latest member
Ratish52

### This Week's Hot Topics

• VBA (Userform)
Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
• List box that changes fill color
Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
• Remove duplicates and retain one. Cross-linked cases
Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
• VBA Copy and Paste With Duplicates
Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
• Macro
is it possible for a macro to run if the active cell value is different to the value above it
• IF DATE and TIME
I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...