SOLVED

New Contributor

# HOW TO: "If cell contains specific text then return specific text"

If I have a list of things, and I want to have the return from another list if it contains certain keywords.

In the appendix of file, the column A is the original list, and I want to have it return "good" in B2 if A2 contains "apple", and return "nice" in B3 if A3 contains "help". Just to search if contains words in column E will return the result in column F.

I noticed from similar questions that I could repeatedly using the if formula to accomplish the goal, but just wondering if have better and neat way to reach the purpose?

`=IF(ISNUMBER(SEARCH("Apple",A2,1)),"good", IF(ISNUMBER(SEARCH("help",A2,1)),"nice", IF(ISNUMBER(SEARCH("water",A2,1)),"better","no match")))`

4 Replies
best response confirmed by allyreckerman (Microsoft)
Solution

# Re: HOW TO: "If cell contains specific text then return specific text"

``=FILTER(\$F\$3:\$F\$5,ISNUMBER(SEARCH(\$E\$3:\$E\$5,A2)))``

# Re: HOW TO: "If cell contains specific text then return specific text"

As variant

``=LOOKUP(2,1/ISNUMBER(SEARCH(\$E\$3:\$E\$5,A2)),\$F\$3:\$F\$5)``