How can I search for all items which references match an input that can be multiple reference separated by a comma in excel?

I am trying to write an excel formula that can output in a single cell all items from a table that matches the target reference. As an example, I have made a table with two columns that looks like the following :

ITEM	           NUMBER

A1                 01
A2                 01
A3                 02
A4                 03
A4                 04
A5                 04
A6                 01
A7                 05
A8                 06
A9                 02
A10                07

Numbers can be attributed to different items simultaneously and items can have more than one number attributed to them.

In this table , I aim to retrieve all items that are related to a number I set as an input.
For example, 01 as an input would give me A1,A2,A6.

This input can also be multiple numbers separated by a comma, and an input set as "02,05" would return all items related to these two numbers : "A3,A7,A9".

i have achieved this in Google Sheets with the following formula :

=JOIN(","; UNIQUE(FILTER(A:A; ARRAYFORMULA(REGEXMATCH(","&B:B&","; ",("&SUBSTITUE(C2; ","; "|")&"),")))))

Where :

• column A corresponds to the Item List
• column B corresponds to the component List
• Cell C2 is where I write my input

This formula works perfectly but I need to have the equivalent on excel for other purposes, and I didn't manage to "convert" this formula without the same functions as in Google Sheets.

There are some ways I found on the net that can output all matching items for a given component but they don't implement the possibility of having multiple inputs separated by a comma.

Would anyone have any idea of how I can do it ? Thank you in advance.

Here is one option:

Formula in E2:

=TEXTJOIN(",",,REPT(A2:A12,ISNUMBER(FIND(B2:B12,D2))))

Or, more dynamic, but fast:

=TEXTJOIN(",",,REPT(TOCOL(A:A,1),ISNUMBER(FIND(TOCOL(B:B,1),D2))))

And, for UNIQUE() values:

=TEXTJOIN(",",,UNIQUE(REPT(TOCOL(A:A,1),ISNUMBER(FIND(TOCOL(B:B,1),D2)))))

This should work since you have formatted each number to the '##' format.

Another:

=TEXTJOIN(",",,FILTER(A2:A12,1-ISNA(XMATCH(B2:B12,TEXTSPLIT(D2,",")))))

Here is another alternative:

• Formula used in cell E2

=TEXTJOIN(", ",,REPT(A2:A12,TEXTSPLIT(D2,",")=B2:B12))

Edit: For UNIQUE Values:

=TEXTJOIN(", ",,UNIQUE(TOCOL(REPT(A2:A12,TEXTSPLIT(D4,",")=B2:B12))))

• Formula used in cell E2

=ARRAYTOTEXT(FILTER(A2:A12,ISNUMBER(XMATCH(B2:B12,TEXTSPLIT(D2,,",")))))

Or,

=LET(
       a,N(TEXTSPLIT(D2,",")=B2:B12)*ROW(B1:B11),
       b,TOCOL(IF(a=0,NA(),a),3),
       TEXTJOIN(", ",,INDEX(A2:A12,b)))