How can I do this lookup?

[chanman]

Hi guys - I need some help returning some data on my spreadsheet
So I have a list of issues with a varying number of comments. In column A I need to show the Date of the latest comment from the user "Helen"
The data in comments in stored as Date/Time ; User ; Comment

Any help appreciated thanks!!

Expected result	Comment1	Comment2	Comment3	Comment4	Comment5	Comment6
01/06/2023	01/Jun/23 13:19 AM;Helen;Hello	05/Jul/23 6:23 PM;Paul;Investigation is in progress
None	26/Jun/23 10:19 AM;Paul;Hello
10/07/2023	05/Jul/23 6:28 PM;Jack;A New Comment	10/Jul/23 6:46 PM;Helen;Help me
12/07/2023	03/Jul/23 6:18 PM;Paul;Will do it tomorrow	04/Jul/23 11:11 AM;Jack;No - do it today	05/Jul/23 6:23 PM;Helen;Quicker please	08/Jul/23 4:22 PM;Helen;Update me now	12/Jul/23 6:46 PM;Helen;Stop ignoring me	15/Jul/23 5:57 PM;Paul;Hello Helen
01/05/2023	01/May/23 5:31 PM;Helen;Boo	26/Jun/23 5:31 PM;Paul;Hi
None	16/Jun/23 12:26 PM;Jack;Hi	20/Jun/23 9:06 AM;Paul;Hello	20/Jun/23 4:00 PM;Jack; Helen says Hi

 

[Fluff]

How about

Fluff.xlsm
	A	B	C	D	E	F	G
1	Expected result	Comment1	Comment2	Comment3	Comment4	Comment5	Comment6
2	01/06/2023	01/Jun/23 13:19 AM;Helen;Hello	05/Jul/23 6:23 PM;Paul;Investigation is in progress
3	None	26/Jun/23 10:19 AM;Paul;Hello
4	10/07/2023	05/Jul/23 6:28 PM;Jack;A New Comment	10/Jul/23 6:46 PM;Helen;Help me
5	12/07/2023	03/Jul/23 6:18 PM;Paul;Will do it tomorrow	04/Jul/23 11:11 AM;Jack;No - do it today	05/Jul/23 6:23 PM;Helen;Quicker please	08/Jul/23 4:22 PM;Helen;Update me now	12/Jul/23 6:46 PM;Helen;Stop ignoring me	15/Jul/23 5:57 PM;Paul;Hello Helen
6	01/05/2023	01/May/23 5:31 PM;Helen;Boo	26/Jun/23 5:31 PM;Paul;Hi
7	None	16/Jun/23 12:26 PM;Jack;Hi	20/Jun/23 9:06 AM;Paul;Hello	20/Jun/23 4:00 PM;Jack; Helen says Hi
Master

Cell Formulas
Range	Formula
A2:A7	A2	=IFERROR(--TEXTBEFORE(TAKE(FILTER(B2:G2,ISNUMBER(FIND(";Helen;",B2:G2))),,-1)," ",,,1),"None")

 

[chanman]

Thanks Fluff - that works a treat, however I missed a step!

It should be " In column A I need to show the Date of the latest comment from a user whos is "Team A"

I have a separate table for user to team:

User	Team
Helen	Team A
David	Team A
Jack	Team B
Paul	Team C

It was left out as I though I'd be able to adjust the forumla to do the lookup myself.... but i dont even know where to start.

 

[Fluff]

How about

Fluff.xlsm
	A	B	C	D	E	F	G
1	Expected result	Comment1	Comment2	Comment3	Comment4	Comment5	Comment6
2	01/06/2023	01/Jun/23 13:19 AM;Helen;Hello	05/Jul/23 6:23 PM;Paul;Investigation is in progress
3	None	26/Jun/23 10:19 AM;Paul;Hello
4	10/07/2023	05/Jul/23 6:28 PM;Jack;A New Comment	10/Jul/23 6:46 PM;Helen;Help me
5	12/07/2023	03/Jul/23 6:18 PM;Paul;Will do it tomorrow	04/Jul/23 11:11 AM;Jack;No - do it today	05/Jul/23 6:23 PM;Helen;Quicker please	08/Jul/23 4:22 PM;Helen;Update me now	12/Jul/23 6:46 PM;David;Stop ignoring me	15/Jul/23 5:57 PM;Paul;Hello Helen
6	01/05/2023	01/May/23 5:31 PM;Helen;Boo	26/Jun/23 5:31 PM;Paul;Hi
7	None	16/Jun/23 12:26 PM;Jack;Hi	20/Jun/23 9:06 AM;Paul;Hello	20/Jun/23 4:00 PM;Jack; Helen says Hi
8
9
10
11
12
13
14	User	Team
15	Helen	Team A
16	David	Team A
17	Jack	Team B
18	Paul	Team C
Master

Cell Formulas
Range	Formula
A2:A7	A2	=IFERROR(--TEXTBEFORE(TAKE(FILTER(B2:G2,BYCOL(ISNUMBER(SEARCH(";"&FILTER($A$15:$A$18,$B$15:$B$18="Team A")&";",B2:G2)),LAMBDA(bc,SUM(--bc)))),,-1)," ",,,1),"None")

 

[chanman]

thank you sir... you are a legend!

 

[Fluff]

You're welcome & thanks for the feedback.