Formula to find words from a list in cell

[Stuepef]

I have a list of words in a range and I would like to create a formula to see if those words are within a cell value. Rather than the formula saying TRUE or FALSE, I would like the formula to provide the actual word that it found OR a numeric value for where that words position is in the list (i.e. if the formula found the 3rd word in the list, then the formula would provide a 3)

Here is the data:

 

[lrobbo314]

How about this?

Excel
	D	E	F
1	List
2	Orange
3	Purple
4	Green
5	Blue
6	Maroon
7	Yellow
8	Light Green
9	Beige
10	Navy Blue
11
12	Cell		Outcome
13	Orange Turbo Car		Orange
14	Orange Turbo Car		Orange
15	Orange Turbo Car		Orange
16	Orange Turbo Car		Orange
17	Purple Lambo		Purple
18	Purple Lambo		Purple
19	Purple Lambo		Purple
20	Beige Seats		Beige
21	Beige Seats		Beige
22	Beige Seats		Beige
Sheet3

Cell Formulas
Range	Formula
F13:F22	F13	=INDEX($D$2:$D$10,AGGREGATE(15,6,ROW($D$2:$D$10)/SEARCH($D$2:$D$10,D13),1)-1)

 

[lrobbo314]

This updated version is a bit better. The old one will fail if the color isn't at the beginning of the sentence.

=INDEX($D$2:$D$10,AGGREGATE(15,6,ROW($D$2:$D$10)/XMATCH("*"&$D$2:$D$10&"*",D13,2),1)-ROW($D$1))

 

[Fluff]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[Stuepef]

This updated version is a bit better. The old one will fail if the color isn't at the beginning of the sentence.

=INDEX($D$2:$D$10,AGGREGATE(15,6,ROW($D$2:$D$10)/XMATCH("*"&$D$2:$D$10&"*",D13,2),1)-ROW($D$1))

Thank you - works great!!

 

[duggie33]

I know it's not part of the OP, but is there a way to return more than one match if found in the string? For instance if D13 = "Orange Turbo Blue Car". Is there a formula that would return "Orange, Blue"? I am imagining the use of TEXTJOIN on a spilled result. I believe I have seen a LAMBDA that will do that...wondering if it can be done with a standard formula.

I am not trying to hijack this thread, just expand on it...

Thanks,

Doug

 

[Fluff]

As you have 365 another option is

Fluff.xlsm
	D	E	F
1	List
2	Orange
3	Purple
4	Green
5	Blue
6	Maroon
7	Yellow
8	Light Green
9	Beige
10	Navy Blue
11
12	Cell
13	Orange Turbo Car		Orange
14	Orange Turbo Car		Orange
15	Orange Turbo Car		Orange
16	Orange Turbo Car		Orange
17	Purple Green Lambo		Purple, Green
18	Purple Lambo		Purple
19	Purple Lambo		Purple
20	Beige Seats		Beige
21	Beige Seats		Beige
22	Beige Seats		Beige
Sheet5

Cell Formulas
Range	Formula
F13:F22	F13	=TEXTJOIN(", ",,XLOOKUP(TEXTSPLIT(D13," "),$D$2:$D$10,$D$2:$D$10,""))

 

[duggie33]

Nice, Fluff.

I came up with an alternate too...

=TEXTJOIN(", ",TRUE,FILTER($D$2:$D$10,ISNUMBER(SEARCH($D$2:$D$10,D13))))

In my mind, I thought there was a way to write a single formula that would spill down the Outcome range, but I think you have to copy the formula down.

Thanks,

Doug

 

[duggie33]

I guess BYROW does that...

=BYROW(D13:D22,LAMBDA(x,TEXTJOIN(", ",TRUE,FILTER($D$2:$D$10,ISNUMBER(SEARCH($D$2:$D$10,x))))))

 

[Stuepef]

As you have 365 another option is

Fluff.xlsm
	D	E	F
1	List
2	Orange
3	Purple
4	Green
5	Blue
6	Maroon
7	Yellow
8	Light Green
9	Beige
10	Navy Blue
11
12	Cell
13	Orange Turbo Car		Orange
14	Orange Turbo Car		Orange
15	Orange Turbo Car		Orange
16	Orange Turbo Car		Orange
17	Purple Green Lambo		Purple, Green
18	Purple Lambo		Purple
19	Purple Lambo		Purple
20	Beige Seats		Beige
21	Beige Seats		Beige
22	Beige Seats		Beige
Sheet5

Cell Formulas
Range	Formula
F13:F22	F13	=TEXTJOIN(", ",,XLOOKUP(TEXTSPLIT(D13," "),$D$2:$D$10,$D$2:$D$10,""))

Thank you - one day I will learn 365 formulas!