index match query

[Redders]

Hi all,

Please help, I'm quite new to index match and really struggling to work out if I'm doing something wrong or if what I'm trying to do is even possible?

I have a couple of Gantt charts, one for the projects and one for the peoples tasks.

On the peoples tasks, I have dates running across the top and people running down the left hand side. In the cells in between, in line with dates and the people, I am adding in the project ID. A project may be worked on multiple days, so the ID could be in multiple cells

What I want to do is to output the last date of the project onto the project Gantt chart. It feels like I need a nested index match to lookup the various items but whatever I try, nothing seems to work.

I've also searched for nested index match to see if this is/n't possible to no avail.

Please can someone help?

TIA

 

[gaz_chops]

Be much better if you post a sample of your data using XL2BB, show what results you are after.

 

[Redders]

This is the source data

Book1
	A	B	C	D	E	F	G	H	I	J
1		01/01/2020	02/01/2020	03/01/2020	04/01/2020	05/01/2020	06/01/2020	07/01/2020	08/01/2020	09/01/2020
2	Person 1	Project 1	Project 1
3	Person 2			Project 2	Project 2
4	Person 3					Project 3	Project 3
5	Person 4			Project 6		Project 6
6	Person 5		Project 5	Project 5
7	Person 6	Project 4	Project 4
8	Person 7		Project 8	Project 8	Project 8	Project 8
9	Person 8				Project 10	Project 10	Project 10
10	Person 9	Project 7	Project 7	Project 7	Project 7	Project 7	Project 7	Project 7	Project 7	Project 7
11	Person 10				Project 9	Project 9	Project 9	Project 9	Project 9
Sheet1

And I would like this output

Book1
	A	B	C
1		Start Date	End Date
2	Project 1
3	Project 2
4	Project 3
5	Project 4
6	Project 5
7	Project 6
8	Project 7
9	Project 8
10	Project 9
11	Project 10
Sheet2

 

[Redders]

Just to add, 1 person wouldn't just have 1 project at a time, they would more than likely have multiple

 

[Redders]

I.E.

Book1
	A	B	C	D	E	F	G	H	I	J
1		01/01/2020	02/01/2020	03/01/2020	04/01/2020	05/01/2020	06/01/2020	07/01/2020	08/01/2020	09/01/2020
2	Person 1	Project 1	Project 1	Project 11	Project 11
3	Person 2			Project 2	Project 2			Project 16
4	Person 3					Project 3	Project 3
5	Person 4			Project 6		Project 6	Project 12
6	Person 5		Project 5	Project 5				Project 15
7	Person 6	Project 4	Project 4
8	Person 7		Project 8	Project 8	Project 8	Project 8
9	Person 8	Project 13			Project 10	Project 10	Project 10
10	Person 9	Project 7	Project 7	Project 7	Project 7	Project 7	Project 7	Project 7	Project 7	Project 7
11	Person 10			Project 14	Project 9	Project 9	Project 9	Project 9	Project 9
Sheet1

 

[Fluff]

How about

+Fluff New.xlsm
	A	B	C
1		Start Date	End Date
2	Project 1	01/01/2020	02/01/2020
3	Project 2	03/01/2020	04/01/2020
4	Project 3	05/01/2020	06/01/2020
5	Project 4	01/01/2020	02/01/2020
6	Project 5	02/01/2020	03/01/2020
7	Project 6	03/01/2020	05/01/2020
8	Project 7	01/01/2020	09/01/2020
9	Project 8	02/01/2020	05/01/2020
10	Project 9	04/01/2020	08/01/2020
11	Project 10	04/01/2020	06/01/2020
Sheet2

Cell Formulas
Range	Formula
B2:B11	B2	=INDEX(Sheet1!$B$1:$J$1,AGGREGATE(15,6,(COLUMN(Sheet1!$B$1:$J$1)-COLUMN(Sheet1!$B$1)+1)/(Sheet1!$B$2:$J$11=$A2),1))
C2:C11	C2	=INDEX(Sheet1!$B$1:$J$1,AGGREGATE(14,6,(COLUMN(Sheet1!$B$1:$J$1)-COLUMN(Sheet1!$B$1)+1)/(Sheet1!$B$2:$J$11=$A2),1))

 

[Redders]

I think that would do it, thank you @Fluff

 

[Fluff]

Glad we could help & thanks for the feedback.

 

[Redders]

I've implemented onto the spreadsheet and it works perfectly. I need to look into this more so that I understand what exactly it is doing. I'm really thankful for your help, if it wasn't for people like you, I wouldn't have been able to learn as much as I have on Excel so far

 

[Fluff]

One way to "See" what a formula is doing, is to use the evaluate formula button on the formulas tab.