Multiple Criteria lookup and return text

[jmewebb]

[TABLE="width: 500"]
<tbody>[TR]
[TD]Current Text (A)[/TD]
[TD]Desired Lookup Result (B)[/TD]
[/TR]
[TR]
[TD]green apple[/TD]
[TD]apple[/TD]
[/TR]
[TR]
[TD]red apple[/TD]
[TD]apple[/TD]
[/TR]
[TR]
[TD]brown banana[/TD]
[TD]banana[/TD]
[/TR]
[TR]
[TD]yellow banana[/TD]
[TD]banana[/TD]
[/TR]
[TR]
[TD]yellow strawberry[/TD]
[TD]strawberry[/TD]
[/TR]
[TR]
[TD]red strawberry[/TD]
[TD]strawberry[/TD]
[/TR]
</tbody>[/TABLE]

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),"")

[TABLE="class: grid, width: 390"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Current Text (A)[/TD]
[TD]Desired Lookup Result (B)[/TD]
[TD][/TD]
[TD]List[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]green apple[/TD]
[TD]apple[/TD]
[TD][/TD]
[TD]apple[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]red apple[/TD]
[TD]apple[/TD]
[TD][/TD]
[TD]banana[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]brown banana[/TD]
[TD]banana[/TD]
[TD][/TD]
[TD]strawberry[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]yellow banana[/TD]
[TD]banana[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]yellow strawberry[/TD]
[TD]strawberry[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]red strawberry[/TD]
[TD]strawberry[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]****[/TD]
[TD]******************[/TD]
[TD]**************************[/TD]
[TD]****[/TD]
[TD]*************[/TD]
[TD]****[/TD]
[/TR]
</tbody>[/TABLE]

Markmzz