Dynamic Lookup for multiple values in a cell (comma separated) and return the corresponding ID to a single cell (comma separated also)

[gtgaabaron]

Hello, I looking for a solution to a similar problem reported here (Lookup multiple values in a single cell (separated by commas) and then return the values to a single cell (also comma separated)).
The thing is not always the amount of values (IDs) will be the same within each cell (at least 1, max=several) that's why the fixed version of using concatenated vlookup+left/mid/right will not work for me and also I'm not able to install any add-in (as mentioned using Morefunc). The only fixed size is the size of the values to lookup (IDs - in green), 8 characters (letters+numbers).

I'm not sure but, is it possible to setup a loop within excel formulas/functions ?
Below is a table containing an example of the issue I'm trying to resolve and the expected values (tables are in different tab). Hope you can help.
Thanks.

 

[KRice]

Here is a formula based approach that relies on two sets of helper cells to split the comma-separated data into parts that can be matched in the main data table using INDEX/MATCH. This is done for both the IDv2 data and the Product data. The results are then recombined using TEXTJOIN. The helper columns accommodate up to 7 items in the original CSV string, but can easily be expanded if necessary. The helper columns can be positioned far to the right or even hidden.

Book3
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
3	IDs	Product	Expected Results		1	2	3	4	5	6	7	1	2	3	4
4	OP512E08,OP513N16	Product55,Product61	FL54Y94,FL83G84		FL54Y94	FL83G84						Product55	Product61
5	OP435R84,OP647C84,OP747B38	Product31,Product48,Product19	FL32G78,FL74L45,FL34P45		FL32G78	FL74L45	FL34P45					Product31	Product48	Product19
6	OP121F45,OP832J43,OP212P67,OP495G74	Product43,Product22,Product74,Product23	FL21A24,FL09S54,FL75D34,FL24Q92		FL21A24	FL09S54	FL75D34	FL24Q92				Product43	Product22	Product74	Product23
7
8
9
10	IDs	Product	IDv2
11	OP512E08	Product55	FL54Y94
12	OP513N16	Product61	FL83G84
13	OP435R84	Product31	FL32G78
14	OP647C84	Product48	FL74L45
15	OP747B38	Product19	FL34P45
16	OP121F45	Product43	FL21A24
17	OP832J43	Product22	FL09S54
18	OP212P67	Product74	FL75D34
19	OP495G74	Product23	FL24Q92
20
gtgaabaron

Cell Formulas
Range	Formula
B4:B6	B4	=TEXTJOIN(",",1,L4:R4)
C4:C6	C4	=TEXTJOIN(",",1,E4:K4)
E4:K6	E4	=IFERROR(INDEX(Table2[IDv2],MATCH(MID($A4,(E$3*9)-8,8),Table2[IDs],0)),"")
L4:O6	L4	=IFERROR(INDEX(Table2[Product],MATCH(MID($A4,(L$3*9)-8,8),Table2[IDs],0)),"")

 

[Amit Tandon]

Enter below Array formula (Ctrl+Shift+Enter) in cell B4 and copy to cell C4 and then cpy down:

=TEXTJOIN(",",,IF(ISNUMBER(MATCH($A$11:$A$50,TRIM(MID(SUBSTITUTE($A4,",",REPT(" ",LEN($A4))),(ROW($1:$255)-1)*LEN($A4)+1,LEN($A4))),0)),B$11:B$50,""))


This considers only column A values (A11:A50) for the multiple comma separated cell A4 values and returns the corresponding column B values (B11:B50) in cell B4 and corresponding column C values (C11:C50) in cell C4.


Regards,
Amit Tandon