Get Items From Descriptions Based on List

[Jeffrey Mahoney]

This is a summarized version of a table that is thousands of rows. I need to find the item from the Type List in Col N that is in the description in Column H. I only need one item per description. The list is ordered correctly.

The formula in Col I is not a solution because I keep having to add new items in the formula. The formula in Col J is good, but is there an easier way? It took me 45 minutes to figure it out and I know my client is going to complain that it's too complicated for them to maintain (which I don't get because the formula doesn't have to be changed, just the type list).

What would be your solution (Cell K3)

Why do these two formulas work in Column L? I'm using MINIFS in cell L2 to get the minimum value over 0. If I wrap the formula in Cell L3 with MINIFS, it doesn't work.
=LET(Ary,COUNTIF(H3,Crit)*SEQUENCE(COUNTA(TypeList)),MINIFS(Ary,Ary,">"&0))

Get Type of Item Sold for BCS.xlsm
	H	I	J	K	L	M	N	O
1		Clunky	Better	Best	Question
2	Description	Type	Type	Type	2		Type List	Crit List
3	AC	AC	AC	??	0		WS	*WS*
4	Tools AC	AC	AC		2		AC	*AC*
5	AC lampshade	AC	AC		0		CB	*CB*
6	Car parts with LED Lights AC	AC	AC		0		TI	*TI*
7	CB Gold Coin	CB	CB
8	Gold half ounce CB	CB	CB
9	CB	CB	CB
10	WS	WS	WS
11	Arizona Coin Shop WS CB	WS	WS
12	TI AC CB WS	WS	WS
13	TI AC CB	AC	AC
14	TI	TI	TI
Sheet1

Cell Formulas
Range	Formula
I3:I14	I3	=IFS(COUNTIF(H3,"*WS*")>0,"WS",COUNTIF(H3,"*AC*")>0,"AC",COUNTIF(H3,"*CB*")>0,"CB",COUNTIF(H3,"*TI*")>0,"TI")
J3:J14	J3	=INDEX(TypeList,MIN(--SUBSTITUTE(COUNTIF(H3,Crit)*SEQUENCE(COUNTA(TypeList)),0,9999)))
L2	L2	=MINIFS(L3#,L3#,">"&0)
L3:L6	L3	=COUNTIF(H3,Crit)*SEQUENCE(COUNTA(TypeList))
O3:O6	O3	="*"&N3&"*"
Dynamic array formulas.

Named Ranges
Name	Refers To	Cells
Crit	=Sheet1!$O$3:$O$6	L3, J3:J14
TypeList	=Sheet1!$N$3:$N$6	L3, J3:J14, O3

 

[StephenCrump]

One possibility :

K3: =INDEX(TypeList,MATCH(1,--ISNUMBER(SEARCH(" "&TypeList&" "," "&H3&" ")),))

I've assumed that the Type List characters will always be capitals (SEARCH is case sensitive), and that in the Descriptions, they will be space separated

You can wrap in IFERROR() if you'd like something other than #N/A if there is no match.

 

[Peter_SSs]

SEARCH is case sensitive

Hi Stephen. I think that you have mixed up SEARCH & FIND features.

@Jeffrey Mahoney
A couple more options. Col L formula is short but col M formula does not need to be copied down.

24 01 24.xlsm
	H	I	J	K	L	M		O	P	Q
1		Clunky	Better	Stephen	Peter 1	Peter 2
2	Description	Type	Type	Type	Type	Type			Type List	Crit List
3	AC	AC	AC	AC	AC	AC			WS	*WS*
4	Tools AC	AC	AC	AC	AC	AC			AC	*AC*
5	AC lampshade	AC	AC	AC	AC	AC			CB	*CB*
6	Car parts with LED Lights AC	AC	AC	AC	AC	AC			TI	*TI*
7	CB Gold Coin	CB	CB	CB	CB	CB
8	Gold half ounce CB	CB	CB	CB	CB	CB
9	CB	CB	CB	CB	CB	CB
10	WS	WS	WS	WS	WS	WS
11	Arizona Coin Shop WS CB	WS	WS	WS	WS	WS
12	TI AC CB WS	WS	WS	WS	WS	WS
13	TI AC CB	AC	AC	AC	AC	AC
14	TI	TI	TI	TI	TI	TI
15	some other text	#N/A	#REF!	#N/A
Lookup

Cell Formulas
Range	Formula
I3:I15	I3	=IFS(COUNTIF(H3,"*WS*")>0,"WS",COUNTIF(H3,"*AC*")>0,"AC",COUNTIF(H3,"*CB*")>0,"CB",COUNTIF(H3,"*TI*")>0,"TI")
J3:J15	J3	=INDEX(TypeList,MIN(--SUBSTITUTE(COUNTIF(H3,Crit)*SEQUENCE(COUNTA(TypeList)),0,9999)))
K3:K15	K3	=INDEX(TypeList,MATCH(1,--ISNUMBER(SEARCH(" "&TypeList&" "," "&H3&" ")),))
L3:L15	L3	=TAKE(FILTER(TypeList,COUNTIF(H3,Crit),""),1)
M3:M15	M3	=BYROW(H3:H15,LAMBDA(rw,TAKE(FILTER(TypeList,COUNTIF(rw,Crit),""),1)))
Q3:Q6	Q3	="*"&P3&"*"
Dynamic array formulas.

Named Ranges
Name	Refers To	Cells
Crit	=Lookup!$Q$3:$Q$6	M3, L3:L15, J3:J15
TypeList	=Lookup!$P$3:$P$6	M3, J3:L15, Q3

 

[StephenCrump]

I think that you have mixed up SEARCH & FIND features.

Ha ha, thanks. Yes, mixed up, muddled and befuddled.

 

[Jeffrey Mahoney]

Peter,

I like the formula in L3. It's clean and easy to read. I wonder why FILTER removes the zeroes without specifying it like: =TAKE(FILTER(TypeList,COUNTIF(H3,Crit)>0,""),1)

There's nothing stated in the materials from MS that state that.
FILTER function - Microsoft Support

 

[Fluff]

Zero is treated as False & any other number as True. This is true with a lot of functions, not just Filter