Lookup formula not working

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


As variant

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