Excel formula that returns comma separated names based on ID's, the formula should allow for as many IDs as possible

Question

I need to create a formula which

This is sheet 1, I need formulas in the second column of this sheet which reads the names from sheet 2 and return the names in a comma separated format: .

This is sheet 2 which contains ID's and corresponding names for these ID's in which Sheet 1 reads from:
.

I would like to do this without VBA, and make use of excel formulas.

Data:

| ID    | Name       |
|-------|------------|
| 8     | apple      |
| 8.1.3 | banana     |
| 8.2   | charlie    |
| 8.3   | delta      |
| 8.4   | echo       |
| 8.5   | foxtrot    |
| 8.5.1 | gamma      |
| 8.5.2 | hello      |
| 8.5.3 | instrument |
| 9     | jump       |
| 9.1.2 | kangaroo   |

Answer 1

The following entered as an array formula with Ctrl + Shift+Enter. I am assuming test string to convert is in E2 and you enter this in F2, you would then drag down.

Credit to @ScottCraner for fixing this.

 =TEXTJOIN(CHAR(44),TRUE, IFERROR(INDEX($B$1:$B$12,N(IF({1},MATCH(FILTERXML("<t><s>" & SUBSTITUTE(E3,CHAR(44), "</s><s>") & "</s></t>", "//s"),$A$1:$A$12,0)))),""))

Answer 2

Use this as an array formula

=TEXTJOIN(", ",TRUE,IF(ISNUMBER(SEARCH(", " & $A$2:$A$12 & ", ",", "& E2 & ", ")),$B$2:$B$12,""))

Being an array formula it must be confirmed with Ctrl-Shift-Enter when exiting edit mode.

This formula assumes there is always a comma AND a space between the numbers. In the lookup.

TEXTJOIN was introduced with Office 365 Excel. Earlier versions will require vba or putting the output in separate cells.