Return for each cell in a range the portion matching a list of string

[ED38]

Hello,

I am trying to build a formula doing the following :
For each cell in the range T2:T38 containing a text string if it includes one of the sub-string listed in the range U2:U12 then show the matching sub-string in a spilled formula as show in column V :
I found a way to do it on ONE cell (my formula in Y2 but I would need a spilled version of a similar formula... )

2024-08-29 SKU Report - Cat code Recommendation.xlsm
	T	U	V	W	X	Y
1	Data	String to search	expected results	Comment		my Try but works only on a single cell not for the array from T2:T38
2	ON_PREMISE_SUB	SUB	SUB			SUB
3	SAAS_WO_CONS	SAAS	CONS
4	EXTSUPP_PLUS	SUPP	SUPP
5	ON_PREMISE_EVAL	EVAL	EVAL
6	PS_PACKAGED	PS	PS
7	ON_PREMISE_PERP	PERP	PERP
8	ELA_SUPPORT_SUMMARY	OTHER	SUPP
9	ELA_LICENSE_SUMMARY	CONS		nothing
10	FOUNDATION_SUPPORT	HW	SUPP
11	ADVSUPP_WO_CONS	EDU	SUPP	because SUPP appears before CONS
12	ON_PREMISE_CONS_PPU	TERM	CONS
13	SAAS_FLEX		SAAS
14	SAAS_PACKAGED		SAAS
15	SAAS_W_CONS		SAAS
16	SAAS_EVAL		SAAS
17	SAAS_BILL_ONLY		SAAS
18	INCIDENT_PACK			nothing
19	HW_SALES			nothing
20	PS_CUSTOM		PS
21	PS_TIME_AND_EXPENSES		PS
22	ON_PREMISE_BILL_ONLY			nothing
23	EDU_CUSTOM		EDU
24	ON_PREMISE_SUB_ACA		SUB
25	EDU_SERVICES		…
26	ON_PREMISE_TERM_FIXED
27	ON_PREMISE_TERM
28	CUSTOM_SUPPORT
29	EDU_ONDEMAND_TRAINING
30	ON_PREMISE_SUB_CONTENT
31	EDU_TIME_AND_EXPENSE
32	SUPP_BILL_ONLY
33	ON_PREMISE_OPENS
34	ON_PREMISE_LIC_EXT
35	SAAS_CUSTOM
36	SAAS_TIME_AND_EXPENSE
37	CREDITS
38	RTS
report1701769401652

Cell Formulas
Range	Formula
Y2	Y2	=@OFFSET($U$1,SUMPRODUCT((COUNTIF(T2,"*"&$U$2:$U$12&"*"))*(ROW(U2:U12)-ROW($U$2)+1)),0,,)

Thanks for any idea if you can help

 

[felixstraube]

Just for fun... another option:

Book1
	A	B	C	D	E	F
1	Data	String to search	expected results	Formula	Formula 2
2	ON_PREMISE_SUB	SUB	SUB	SUB	SUB
3	SAAS_WO_CONS	SAAS	CONS	SAAS, CONS	SAAS	CONS
4	EXTSUPP_PLUS	SUPP	SUPP	SUPP	SUPP
5	ON_PREMISE_EVAL	EVAL	EVAL	EVAL	EVAL
6	PS_PACKAGED	PS	PS	PS	PS
7	ON_PREMISE_PERP	PERP	PERP	PERP	PERP
8	ELA_SUPPORT_SUMMARY	OTHER	SUPP	SUPP	SUPP
9	ELA_LICENSE_SUMMARY	CONS
10	FOUNDATION_SUPPORT	HW	SUPP	SUPP	SUPP
11	ADVSUPP_WO_CONS	EDU	SUPP	SUPP, CONS	SUPP	CONS
12	ON_PREMISE_CONS_PPU	TERM	CONS	CONS	CONS
13	SAAS_FLEX		SAAS	SAAS	SAAS
14	SAAS_PACKAGED		SAAS	SAAS	SAAS
15	SAAS_W_CONS		SAAS	SAAS, CONS	SAAS	CONS
16	SAAS_EVAL		SAAS	SAAS, EVAL	SAAS	EVAL
17	SAAS_BILL_ONLY		SAAS	SAAS	SAAS
18	INCIDENT_PACK
19	HW_SALES			HW	HW
20	PS_CUSTOM		PS	PS	PS
21	PS_TIME_AND_EXPENSES		PS	PS	PS
22	ON_PREMISE_BILL_ONLY
23	EDU_CUSTOM		EDU	EDU	EDU
24	ON_PREMISE_SUB_ACA		SUB	SUB	SUB
25	EDU_SERVICES		…	EDU	EDU
26	ON_PREMISE_TERM_FIXED			TERM	TERM
27	ON_PREMISE_TERM			TERM	TERM
28	CUSTOM_SUPPORT			SUPP	SUPP
29	EDU_ONDEMAND_TRAINING			EDU	EDU
30	ON_PREMISE_SUB_CONTENT			SUB	SUB
31	EDU_TIME_AND_EXPENSE			EDU	EDU
32	SUPP_BILL_ONLY			SUPP	SUPP
33	ON_PREMISE_OPENS
34	ON_PREMISE_LIC_EXT
35	SAAS_CUSTOM			SAAS	SAAS
36	SAAS_TIME_AND_EXPENSE			SAAS	SAAS
37	CREDITS
38	RTS
Sheet1

Cell Formulas
Range	Formula
D2:D38	D2	=LET(d,A2:A38, ss,B2:B12, DROP(REDUCE("",d,LAMBDA(arr,newItem, VSTACK(arr, IFERROR(TEXTJOIN(", ", 1,FILTER(ss, ISNUMBER(SEARCH(ss, newItem)))), "")))), 1) )
E2:F38	E2	=LET(d,A2:A38, ss,B2:B12, IFERROR(DROP(REDUCE("",d,LAMBDA(arr,newItem, VSTACK(arr, IFERROR(TOROW(FILTER(ss, ISNUMBER(SEARCH(ss, newItem)))), "")))), 1), "") )
Dynamic array formulas.