index match query

Redders

New Member
Joined
Sep 19, 2015
Messages
14
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
 

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)

gaz_chops

Well-known Member
Joined
Apr 29, 2003
Messages
6,485
Platform
  1. MacOS
Be much better if you post a sample of your data using XL2BB, show what results you are after.
 

Redders

New Member
Joined
Sep 19, 2015
Messages
14
This is the source data

Book1
ABCDEFGHIJ
101/01/202002/01/202003/01/202004/01/202005/01/202006/01/202007/01/202008/01/202009/01/2020
2Person 1Project 1Project 1
3Person 2Project 2Project 2
4Person 3Project 3Project 3
5Person 4Project 6Project 6
6Person 5Project 5Project 5
7Person 6Project 4Project 4
8Person 7Project 8Project 8Project 8Project 8
9Person 8Project 10Project 10Project 10
10Person 9Project 7Project 7Project 7Project 7Project 7Project 7Project 7Project 7Project 7
11Person 10Project 9Project 9Project 9Project 9Project 9
Sheet1


And I would like this output

Book1
ABC
1Start DateEnd Date
2Project 1
3Project 2
4Project 3
5Project 4
6Project 5
7Project 6
8Project 7
9Project 8
10Project 9
11Project 10
Sheet2
 

Redders

New Member
Joined
Sep 19, 2015
Messages
14
Just to add, 1 person wouldn't just have 1 project at a time, they would more than likely have multiple
 

Redders

New Member
Joined
Sep 19, 2015
Messages
14

ADVERTISEMENT

I.E.
Book1
ABCDEFGHIJ
101/01/202002/01/202003/01/202004/01/202005/01/202006/01/202007/01/202008/01/202009/01/2020
2Person 1Project 1Project 1Project 11Project 11
3Person 2Project 2Project 2Project 16
4Person 3Project 3Project 3
5Person 4Project 6Project 6Project 12
6Person 5Project 5Project 5Project 15
7Person 6Project 4Project 4
8Person 7Project 8Project 8Project 8Project 8
9Person 8Project 13Project 10Project 10Project 10
10Person 9Project 7Project 7Project 7Project 7Project 7Project 7Project 7Project 7Project 7
11Person 10Project 14Project 9Project 9Project 9Project 9Project 9
Sheet1
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
62,466
Office Version
  1. 365
Platform
  1. Windows
How about
+Fluff New.xlsm
ABC
1Start DateEnd Date
2Project 101/01/202002/01/2020
3Project 203/01/202004/01/2020
4Project 305/01/202006/01/2020
5Project 401/01/202002/01/2020
6Project 502/01/202003/01/2020
7Project 603/01/202005/01/2020
8Project 701/01/202009/01/2020
9Project 802/01/202005/01/2020
10Project 904/01/202008/01/2020
11Project 1004/01/202006/01/2020
Sheet2
Cell Formulas
RangeFormula
B2:B11B2=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:C11C2=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

New Member
Joined
Sep 19, 2015
Messages
14

ADVERTISEMENT

I think that would do it, thank you @Fluff
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
62,466
Office Version
  1. 365
Platform
  1. Windows
Glad we could help & thanks for the feedback.
 

Redders

New Member
Joined
Sep 19, 2015
Messages
14
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
62,466
Office Version
  1. 365
Platform
  1. Windows
One way to "See" what a formula is doing, is to use the evaluate formula button on the formulas tab.
 

Forum statistics

Threads
1,141,479
Messages
5,706,621
Members
421,460
Latest member
Taamrak

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top