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

 

[Fluff]

How about

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

Cell Formulas
Range	Formula
V2:V38	V2	=MAP(T2:T38,LAMBDA(m,TAKE(FILTER(U2:U12,ISNUMBER(SEARCH(U2:U12,m)),""),1)))
Dynamic array formulas.

Although I get different results from what you showed.

 

[Kaper]

A bit long formula (probably could be compacted with LET), but seems to return right results).

=IFERROR(TEXTSPLIT(BYROW(IF(LEN(T2:T38)-LEN(SUBSTITUTE(T2:T38,TRANSPOSE(U2:U12),""))>0,TRANSPOSE(U2:U12),""),LAMBDA(arr,TEXTJOIN(";",,arr))),";",",",TRUE),"")

 

[Fluff]

but seems to return right results

That returns the same as the formula I suggested, which is not what the OP showed as being correct.

 

[ED38]

Thank again Fluff.
It's exactly what I expected
No worries for the different results, I will manage the details.
Very happy and lucky you care my requests.
you're my "excel guardian angel"!

 

[Fluff]

Glad we could help & thanks for the feedback.

 

[ED38]

A bit long formula (probably could be compacted with LET), but seems to return right results).

=IFERROR(TEXTSPLIT(BYROW(IF(LEN(T2:T38)-LEN(SUBSTITUTE(T2:T38,TRANSPOSE(U2:U12),""))>0,TRANSPOSE(U2:U12),""),LAMBDA(arr,TEXTJOIN(";",,arr))),";",",",TRUE),"")

Thank you Kaper for your time as well (I was also looking for something using the "byrow" function ). It seems like you did it

 

[Fluff]

In this instance both Map & Byrow do the same thing, so you could just swap Map for Byrow in the formula I supplied & it will return the same answers.

 

[ED38]

In this instance both Map & Byrow do the same thing, so you could just swap Map for Byrow in the formula I supplied & it will return the same answers.

Good to know. Thank you again.

 

[Fluff]

You're welcome.