Formula Conversion to Office 2016

Greetings!

A friend of mine, who works in IT, created a formula for a problem that was also dealt with here, by some of the helpful moderators of this forum. However, contrary to the original information I had, our version of Excel is not the Microsoft 365, but the Office 2016.

Is it possible to create a version of this formula for Office 2016?
=TEXTJOIN(";"; FALSE; FILTER(TEXTSPLIT(A6;;";"; TRUE); ISNUMBER(MATCH(TEXTSPLIT(A6;;";";TRUE); F:F; 0)); "No matches"))

A brief description of what the formula is supposed to do:

It compares the leaders' names table with the contents of the cells in the teams column, and displays the leader names who are in those teams in the coordinators column - as it is presented in the linked sample sheet.SampleSheet.xlsx

P.S.: I apologize to the moderators of this forum who worked with the incorrect information I have given. Your work is much appriciated and helped us greatly with understanding and hopefully solving the problem.

1. Are you allowed to use VBA?

2. Is the use of helper column(s) allowed?

Insert into a module:

Please Login or Register  to view this content.

Then use this array formula (CTRL-SHIFT-ENTER), copied down:

concatall(IF(ISNUMBER(MATCH($F$2:$F$6,FILTERXML("<A><B>"&SUBSTITUTE(A2,"; ","</B><B>")&"</B></A>","//B")&{""},0)),INDEX(F:F,ROW($F$2:$F$6)),""),"; ")

There is no UDF in that workbook.

1. You may have pasted the code into a module, but you have to save the file as macro-enabled (.xlsm). You saved it as an xlsx and the module was deleted.

2. The formula was entered into a range of cells. It should have been entered into 1 cell with CTRL-SHIFT-ENTER and copied down.

Now fixed.