If cell contains a word from range, then return that word (not TRUE or FALSE)

[northw]

Hello,

I found something that is close to what I need, but instead of returning True/False, I need the actual word. Please see screenshot as an example.

And here is the link close to find I need, but returns T/F.
Cell contains many things

Thanks!

 

[kweaver]

One approach but that assumed the color was the leading word in the item string.

Book2
	A	B	C	D
1	Item	Result		Keywords
2	Green t-shirt	Green		Green
3	Blue trousers	Blue		Blue
4	Yellow socks	Yellow		Yellow
5	Black Tie	Not Found		Orange
Sheet1

Cell Formulas
Range	Formula
B2:B5	B2	=IFERROR(INDEX($D$1:$D$5,MATCH(LEFT(A2,FIND(" ",A2)-1),$D$1:$D$5,0)),"Not Found")

 

[Momentman]

Something like this

SheetNavigator.xlsm
	B	C	D	E
1	items	Result		Keyword Range
2	Green T-Shirt	Green		Green
3	my Blue Trousers	Blue		Blue
4	Yellow Socks	yellow		yellow
5	Black Tie	Not found		orange
Sheet9

Cell Formulas
Range	Formula
C2:C5	C2	=IFERROR(INDEX($E$2:$E$5,MATCH(TRUE,SEARCH($E$2:$E$5,B2)>0,0)),"Not found")

 

[northw]

Thanks for the answer @kweaver , I should have thought about my example. The keyword (colours in this instance) could indeed be anywhere in the items. For example, it could be "Leave pattern brown scarf".

Do you think this is could be achievable?

Thank you for your time.

 

[northw]

@Momentman This seems to work great, thank you!! I've got so much to learn

 

[kweaver]

Maybe try this VBA:

Dim i As Long, w As Long, j As Long, j2 As Long
j = Cells(Rows.Count, 4).End(xlUp).Row
For i = 2 To Cells(Rows.Count, 1).End(xlUp).Row
 For j2 = 2 To j
 w = InStr(1, Cells(i, 1), Cells(j2, 4), vbTextCompare)
 If w <> 0 Then
 Cells(i, 2) = Cells(j2, 4)
 GoTo found
 Else
 Cells(i, 2) = "Not Found"
 End If
 Next j2
found: Next i
End Sub