Formula for getting the latest date using part of the text as criteria

[avk5021]

lets say I have information of workers working on different items, some items are made with multiple workers. And there are start date and end date as shown in the table below.

start date	end date	workers
1 jan 21	10 feb 21	A + B
11 feb 21	20 feb 21	B
11 feb 21	1 mar 21	A + C
2 mar 21	15 apr 21	C

Here is the result I would like to achieve using a formula.

Worker	end date
A	1 mar 21
B	20 feb 21
C	15 apr 21

This is so I can check when each worker will be done with their tasks.

 

[AlanY]

try this

Book1
	A	B	C
1	start date	end date	workers
2	01-Jan-21	10-Feb-21	A + B
3	11-Feb-21	20-Feb-21	B
4	11-Feb-21	01-Mar-21	A + C
5	02-Mar-21	15-Apr-21	C
6
7	A	01-Mar-21
8	B	20-Feb-21
9	C	15-Apr-21
Sheet1

Cell Formulas
Range	Formula
B7:B9	B7	=MAXIFS($B$2:$B$5,$C$2:$C$5,"*"&A7&"*")

 

[avk5021]

try this

Book1
	A	B	C
1	start date	end date	workers
2	01-Jan-21	10-Feb-21	A + B
3	11-Feb-21	20-Feb-21	B
4	11-Feb-21	01-Mar-21	A + C
5	02-Mar-21	15-Apr-21	C
6
7	A	01-Mar-21
8	B	20-Feb-21
9	C	15-Apr-21
Sheet1

Cell Formulas
Range	Formula
B7:B9	B7	=MAXIFS($B$2:$B$5,$C$2:$C$5,"*"&A7&"*")

my Excel 2013 doesn't have =MAXIFS function. Is there anyway I can add this function? or other formula that would work on 2013 excel version?

 

[Dave Patton]

Aggregate.xlsm
	A	B	C
1	start date	end date	workers
2	1-Jan-21	10-Feb-21	A + B
3	11-Feb-21	20-Feb-21	B
4	11-Feb-21	1-Mar-21	A + C
5	2-Mar-21	15-Apr-21	C
6
7	A		1-Mar-21
8	B		20-Feb-21
9	C		15-Apr-21
10
6a

Cell Formulas
Range	Formula
C7:C9	C7	=AGGREGATE(14,6,$B$2:$B$5/(SEARCH(A7,$C$2:$C$5)=1),1)

 

[avk5021]

I have switched to Office 365, Now the maxifs function works perfectly. Thanks all for your help!

 

[Dave Patton]

Did you try my suggestion???

Thanks for the feedback!