If countif array formula

QuestionEdit

Hi Isaac,

I am struggling here and hope you can help.

I am trying to see if the value in one cell appears in any part of the string in another cell, eg Does WASHING MACHINE (stored in cell A1) appear anywhere in AUTOMATIC WASHING MACHINE AW123 (in B2) and if it does to return WASHING MACHINE. I have managed this with the following:

=IF(COUNTIF(B1,"**"&A1&"**")>0,"A1","Nothing")

Now what I want to do is check for multiple values ie A:A not just A1. I tried

{=IF(COUNTIF(B1,"**"&A:A&"**")>0,"Found","Nothing")}

as I couldn't work out how to tell it to return the matching value so thought I'd try to get it to tell me it had found it at all, but it only comes back "Found" if the value is in A1.

Am I way off with my array formula? I only worked out I might need one from reading other answers posted here.

I don't know if you use Access but if there is an easier way to do this in that then feel free to make suggestions there.

I hope you can help as I have googled all the questions I can think of!

AnswerEdit

To be honest with you I rarely use Array formulas - not because I think i am smarter and have found better way, just because are complicated, take forever to calculate in many cases, depending on rows/columns used (can really kill a workbook).

How about just something simpler; this works for me I tested it according to the kind of situation you describe:

=IF(COUNTIF($B$1:$B$1000,"*"&A1&"*")>0,A1,"Nothing")