I am trying to compare two cells containing strings. I can't use VBA for restrictive reasons on my computer so it's just formulas for me. This is what I have:

One cell is there to enter lots of text to explain a problem statement. Another cell is nearby containing a handful of keywords - may or may not be separated by commas (depending on what works!). So an example...

The product keyword cell might have: Camera, torch, messenger, internet

In the problem description cell I might write: "I am after a phone that can take good pictures with a 50mp camera and I can use the internet to look at the news"

The solution I'm after...In another cell I want to return the value of TRUE, if even one or more than one word matches - I don't need to know how many matches there were, just if there was a match somewhere and return TRUE.

CodePudding user response：

You're asking essentially for an OR condition. From there just a couple lookup functions. Find will search case sensative, while Search will not.

Depending on your version of excel, you could use this for a dynamic solution with B2 being your sentance and B3 being your list of words separated by a comma (make sure spaces arent an issue).

=OR(ISNUMBER(SEARCH(TEXTSPLIT(B3,","),B2)))

Or a more hard-coded solution for older versions... of excel that just puts the words in the formula

=OR(ISNUMBER(SEARCH("Camera",B2)),ISNUMBER(SEARCH("torch",B2)),
ISNUMBER(SEARCH("messenger",B2)),ISNUMBER(SEARCH("internet",B2)))