Multiple Criteria lookup and return text

[jmewebb]

Current Text (A)	Desired Lookup Result (B)
green apple	apple
red apple	apple
brown banana	banana
yellow banana	banana
yellow strawberry	strawberry
red strawberry	strawberry

<tbody>
</tbody>

Column A contains extended text with the key word. I want to return the key word in column B. I have three keys words I am searching for in the data.

 

[Joe4]

For an entry in cell A2 (formula to put in cell B2 and copy down):

=IF(ISNUMBER(SEARCH("apple",A2)),"apple",IF(ISNUMBER(SEARCH("banana",A2)),"banana",IF(ISNUMBER(SEARCH("strawberry",A2)),"strawberry","")))

 

[jmewebb]

That's what I needed! Thank you!

 

[Joe4]

You are welcome.

 

[markmzz]

Another way.

You can try this too:

=IFERROR(LOOKUP(1,1/SEARCH(" "&$D$2:$D$4&" "," "&A2&" "),$D$2:$D$4),"")

	A	B	C	D	E
1	Current Text (A)	Desired Lookup Result (B)		List
2	green apple	apple		apple
3	red apple	apple		banana
4	brown banana	banana		strawberry
5	yellow banana	banana
6	yellow strawberry	strawberry
7	red strawberry	strawberry
8
****	******************	**************************	****	*************	****

<tbody>
</tbody>

Markmzz