Match/Index Formula

[Matalyn]

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]

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]

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]

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]

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

 

[exo33]

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.

 

[exo33]

Image codes: solution

 

[Andrew Poulsom]

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]

Works great!! Thanks