Return value from another cell when value(s) is present as part of a string

[Georgez]

Hello Experts.
I appreciate any help you can provide. I am trying to list users Secretaries in a spreadsheet
I have a spreadsheet containing user information (initials, name, title etc). If the user is a Secretary, their title includes the initials of the people they work for separated by a comma.
I need to generate the Secretaries initials against the users based on the presence of the users initials in the Secretaries titles. NB some users have more than one secretary.
I am able to use INDEX / MATCH but can't work out how to do this in this scenario, when the users initials are part of a string, and have possible multiple matches.
I have attached an image of some dummy records to illustrate. I am trying to generate the information held in column G.
Many thanks for any guidance.

 

[dreid1011]

What version of Excel are you using?

 

[Georgez]

I am using Office 365. Full version below:
Microsoft® Excel® for Microsoft 365 MSO (Version 2308 Build 16.0.16731.20496) 32-bit

 

[PeteWright]

One question, would you mind having a user-defined function (UDF) in VBA?
Or does it have to be pure Excel?

I have tried some things, but even with some Array Functions I could not get a result yet.
The "multiple matches" thing seems challenging.

I already figured out how to get the first Secretary for a user, but not yet if there are more.
So before I try something else, let me know if you're OK with some VBA.

 

[dreid1011]

I am using Office 365. Full version below:
Microsoft® Excel® for Microsoft 365 MSO (Version 2308 Build 16.0.16731.20496) 32-bit

With that said, give this a try:

Book1
	A	B	C	D	E	F	G
1	Reference	First Name	Last Name	Display Name	Email	Role	Sec Ref
2	ABC					Trainee Solicitor	XYZ / SLT
3	BCD					Partner
4	XYZ					Deputy ASSM / Secretary to ABC, RFS
5	STH					Paralegal
6	MAN					Paralegal	SLT
7	SLT					Secretary to MAN, RAW, ABC
8	RFS					Senior Associate	XYZ
9	RAW					Associate	SLT
Sheet1

Cell Formulas
Range	Formula
G2:G9	G2	=IFERROR(TEXTJOIN(" / ",TRUE,FILTER($A$2:$A$9,ISNUMBER(SEARCH(A2,$F$2:$F$9)))),"")

 

[Georgez]

Thank you everyone! I am happy with VBA if that's what it takes.
@dreid1011 many thanks for your code. This is great, and is working however if a job title contains the text string it is also bringing back a match. Eg MAR will be a hit on anyone with "Marketing" in their title. All the user initials are three characters so is it possibel to limit the match to three characters or inlcude ", " in the criteria?
Thanks again!

 

[Fluff]

I am using Office 365.

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[Fluff]

How about

=TEXTJOIN(" / ",TRUE,FILTER($A$2:$A$9,ISNUMBER(SEARCH(" "&A2&","," "&$F$2:$F$9&",")),""))

 

[Georgez]

Thank you everyone! @Fluff - your suggestion has done the trick. Thank you and everyone else for your expertise!

 

[Fluff]

Glad we could help & thanks for the feedback.