Here's a formula that will work in Excel 2016, as is. In earlier versions of Excel, a poly-fill UDF for `TEXTJOIN()`

is required. (See this post for a basic one.)

Array enter (`Ctrl`+`Shift`+`Enter`) the following formula in `F2`

and copy-paste/fill-down into the rest of the column:

```
{=TEXTJOIN(";",TRUE,INDEX(B:B,N(IF(1,MATCH(--MID(SUBSTITUTE(E2,";",REPT(" ",99)),(ROW(OFFSET($A$1,,,LEN(E2)-LEN(SUBSTITUTE(E2,";",""))+1))-1)*99+((ROW(OFFSET($A$1,,,LEN(E2)-LEN(SUBSTITUTE(E2,";",""))+1)))=1),99),A:A,0)))))}
```

Note that this formula only works if the values in column `A`

are actually stored as numbers. For text values, the `--MID(…)`

in the formula needs to be replaced by `TRIM(MID(…))`

.

The prettified formula is as follows:

```
{=
TEXTJOIN(
";",
TRUE,
INDEX(
(B:B),
N(IF(1,
MATCH(
--MID(
SUBSTITUTE(E2,";",REPT(" ",99)),
99*(ROW(OFFSET($A$1,,,LEN(E2)-LEN(SUBSTITUTE(E2,";",""))+1))-1)
+(1=ROW(OFFSET($A$1,,,LEN(E2)-LEN(SUBSTITUTE(E2,";",""))+1))),
99
),
(A:A),
0
)
))
)
)}
```

**Notes:**

- The prettified formula actually works if entered.
- The brackets around
`(A:A)`

in the prettified version are required to force the `A:A`

to remain on its own line. The same applies for the `(B:B)`

.

For Excel 2016 (Windows only) the following simpler formula should work:

```
{=TEXTJOIN(";",TRUE,INDEX(B:B,N(IF(1,MATCH(--FILTERXML("<a><b>" & SUBSTITUTE(E2, ";", "</b><b>") & "</b></a>", "//b"),A:A,0)))))}
```

Just like the previous formula, this one also only works on values stored as numbers. For text values, just remove the `--`

from the formula.