Match/Index Formula

Matalyn

New Member
Joined
Oct 17, 2014
Messages
24
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
Joined
Feb 17, 2007
Messages
3,707
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
Joined
Oct 17, 2014
Messages
24
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
Joined
Jul 21, 2002
Messages
73,092
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
Joined
Oct 17, 2014
Messages
24
That simply extracted all the data on the Project Sheet rather than those just assigned to Joe.
 

exo33

New Member
Joined
Oct 29, 2014
Messages
31
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
Joined
Jul 21, 2002
Messages
73,092
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.
 

Forum statistics

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

Some videos you may like

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