Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

Are column B cells hidden somewhere anywhere in column C? Give results in column A. (true/false or yes/no) I'm unable to post the simple Excel image I've created as I'm a guest, but it shows simply what's needed.

Col A has formula =not(isna(match(b2,$c$1:$c$1000,FALSE))) Obviously this does show a TRUE result if abc is in both column B and columns C, but I need it to find abc in column C if abc is mixed in with others, such as xxxabcxxx.
Column B cells coming down are simply abc def efg Column C show xxxxabcxxx xxdefffxxx xxxxxxefgxxx and so on.

share|improve this question
    
Welcome to SuperUser. As it stands this question is very confusing to follow. Could you edit it and add an example? As it stands now it would be difficult to answer. –  Brad Patton May 17 '13 at 15:23

2 Answers 2

You want the FIND(text, within_text, start_char) function.

NOT(ISERROR(FIND(b2, c2)))

Will return TRUE if the contents of b2 are somewhere inside c2:

NOT(ISERROR(FIND("abc","xxxabcxxx")))

If you need case-insensitive matching, use the SEARCH function instead.

share|improve this answer
    
I appreciate the quick response, thanks!! So I changed my formula to =NOT(ISERROR(FIND(B2,$C$2:$C$1000))) and got all FALSE results. What am I missing, please?? Keep in that I can't put in "abc", the cells are names of companies and every cell is different in column B. Column C is another row of companies from another data base and I'm actually having to put a whole range (C1 to C1000) where your example says xxxabcxxx. make sense?? I sure wish I could just post the simple image... –  Crush May 17 '13 at 15:15
    
got it.... =NOT(ISNA(INDEX(C2:C100,MATCH(""&B2&"",C2:C100,0)))) thanks again for trying to help out, and have a great day... –  Crush May 17 '13 at 15:51

You can modify your original formula so that it matches on strings embedded in other strings:

  =NOT(ISNA(MATCH("*"&B2&"*",$C$1:$C$1000,FALSE)))

The asterisk (*) is one of the wildcard characters for Excel lookup functions. It matches any number of characters. The other is the question mark (?), which matches any single character.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.