SOLVED

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

%3CLINGO-SUB%20id%3D%22lingo-sub-2679849%22%20slang%3D%22en-US%22%3ERe%3A%20HOW%20TO%3A%20%22If%20cell%20contains%20specific%20text%20then%20return%20specific%20text%22%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2679849%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1136348%22%20target%3D%22_blank%22%3E%40Lucascobar%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel-formula%22%3E%3CCODE%3E%3DFILTER(%24F%243%3A%24F%245%2CISNUMBER(SEARCH(%24E%243%3A%24E%245%2CA2)))%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2679701%22%20slang%3D%22en-US%22%3EHOW%20TO%3A%20%22If%20cell%20contains%20specific%20text%20then%20return%20specific%20text%22%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2679701%22%20slang%3D%22en-US%22%3E%3CP%3EIf%20I%20have%20a%20list%20of%20things%2C%20and%20I%20want%20to%20have%20the%20return%20from%20another%20list%20if%20it%20contains%20certain%20keywords.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIn%20the%20appendix%20of%20file%2C%20the%20column%20A%20is%20the%20original%20list%2C%20and%20I%20want%20to%20have%20it%20return%20%22good%22%20in%20B2%20if%20A2%20contains%20%22apple%22%2C%20and%20return%20%22nice%22%20in%20B3%20if%20A3%20contains%20%22help%22.%20Just%20to%20search%20if%20contains%20words%20in%20column%20E%20will%20return%20the%20result%20in%20column%20F.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20noticed%20from%20similar%20questions%20that%20I%20could%20repeatedly%20using%20the%20if%20formula%20to%20accomplish%20the%20goal%2C%20but%20just%20wondering%20if%20have%20better%20and%20neat%20way%20to%20reach%20the%20purpose%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%3E%3DIF(ISNUMBER(SEARCH(%22Apple%22%2CA2%2C1))%2C%22good%22%2C%3CBR%20%2F%3E%20IF(ISNUMBER(SEARCH(%22help%22%2CA2%2C1))%2C%22nice%22%2C%3CBR%20%2F%3E%20IF(ISNUMBER(SEARCH(%22water%22%2CA2%2C1))%2C%22better%22%2C%3CBR%20%2F%3E%22no%20match%22)))%3CBR%20%2F%3E%3CBR%20%2F%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2679701%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2679988%22%20slang%3D%22en-US%22%3ERe%3A%20HOW%20TO%3A%20%22If%20cell%20contains%20specific%20text%20then%20return%20specific%20text%22%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2679988%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1136348%22%20target%3D%22_blank%22%3E%40Lucascobar%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EAs%20variant%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-excel-formula%22%3E%3CCODE%3E%3DLOOKUP(2%2C1%2FISNUMBER(SEARCH(%24E%243%3A%24E%245%2CA2))%2C%24F%243%3A%24F%245)%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E
New Contributor

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

@Lucascobar 

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

@Lucascobar 

As variant

=LOOKUP(2,1/ISNUMBER(SEARCH($E$3:$E$5,A2)),$F$3:$F$5)
Very helpful, thanks so much!
Thanks for the quick solution!!