Lookup formula not working

%3CLINGO-SUB%20id%3D%22lingo-sub-2282786%22%20slang%3D%22en-US%22%3ELookup%20formula%20not%20working%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2282786%22%20slang%3D%22en-US%22%3E%3CP%3EHello%20all%20-%20Hoping%20all%20safe!%3C%2FP%3E%3CP%3EI%20am%20trying%20to%20use%20a%20lookup%20formula%20to%20check%20if%20a%20text%20string%20is%20present%20in%20a%20column%20cell%2C%20and%20if%20so%2C%20it%20returns%20a%20value%20TRUE%20in%20a%20different%20column%20but%20the%20same%20row.%26nbsp%3B%3C%2FP%3E%3CP%3Efor%20example%3A%20checking%20for%20a%20string%20%22Need%20Material%22%20in%20cell%20B3%2C%20should%20return%20a%20value%20TRUE%20in%20a%20cell%20K3%20and%20so%20on%20for%20all%20other%20rows%20respectively.%3C%2FP%3E%3CP%3ECell%20source%20(ex%3A%20B3)%20may%20contain%20multiple%20strings%20including%20the%20one%20for%20which%20is%20search%20is%20to%20be%20made%3A%20Example%3A%3C%2FP%3E%3CP%3EB3%20%3D%3D%20Missing%20Router%2C%20%3CSTRONG%3ENeed%20Material%3C%2FSTRONG%3E%2C%20Labor%20Shortage%3C%2FP%3E%3CP%3EC3%20%3D%3D%20Need%20Material%2C%20Labor%20Shortage%3CBR%20%2F%3ED3%20%3D%3D%20Labor%20Shortage%3C%2FP%3E%3CP%3Eetc%20etc.%20and%20in%20any%20combination%20of%20the%20possible%20values.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3DLOOKUP(2%5E15%2CSEARCH(%7B%22Need%20Material%7D%2C%24B3)%2C%7B%22TRUE%22%7D)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESomehow%20the%20formula%20is%20returning%20a%20FALSE%20value%20even%20if%20B3%20has%20the%20string%20%22Need%20Material%22%20is%20existing.%20Any%20advise%3F%3C%2FP%3E%3CP%3EThank%20you!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2282786%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2283059%22%20slang%3D%22en-US%22%3ERe%3A%20Lookup%20formula%20not%20working%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2283059%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1035098%22%20target%3D%22_blank%22%3E%40stctabt%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EAs%20variant%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DCOUNTIF(%24B3%3A%24J3%2C%20%22*Need%20Material*%22)%26gt%3B0%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2282969%22%20slang%3D%22en-US%22%3ERe%3A%20Lookup%20formula%20not%20working%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2282969%22%20slang%3D%22en-US%22%3EIt%20returns%20%22TRUE%22%20for%20me.%20But%2C%20you%20should%20be%20able%20to%20simplify%20that%20formula%20to%3A%3CBR%20%2F%3E%3CBR%20%2F%3E%3DISNUMBER(SEARCH(%22Need%20Material%22%2C%24B3))%3C%2FLINGO-BODY%3E
Occasional Visitor

Hello all - Hoping all safe!

I am trying to use a lookup formula to check if a text string is present in a column cell, and if so, it returns a value TRUE in a different column but the same row. 

for example: checking for a string "Need Material" in cell B3, should return a value TRUE in a cell K3 and so on for all other rows respectively.

Cell source (ex: B3) may contain multiple strings including the one for which is search is to be made: Example:

B3 == Missing Router, Need Material, Labor Shortage

C3 == Need Material, Labor Shortage
D3 == Labor Shortage

etc etc. and in any combination of the possible values. 

 

=LOOKUP(2^15,SEARCH({"Need Material},$B3),{"TRUE"})

 

Somehow the formula is returning a FALSE value even if B3 has the string "Need Material" is existing. Any advise?

Thank you!

2 Replies
It returns "TRUE" for me. But, you should be able to simplify that formula to:

=ISNUMBER(SEARCH("Need Material",$B3))

@stctabt 

As variant

=COUNTIF($B3:$J3, "*Need Material*")>0