Dear Analyst #133: Find or check if a cell contains text from a list of values or partial matching text in a list (3 methods)

There are times when a problem nags at you like a pebble in your shoe. Even when you’re not working on the problem itself, you’re thinking about the problem which make it even worse. Rarely do I come across a Google Sheets/Excel task I cannot solve with formulas, but this one evaded my extensive knowledge (or lack thereof). The reason why this problem kept on nagging at me was because it felt like it should be easy to do. Like it was something I’ve solved before in the past. Or a data manipulation task that should be easy to solve. Yet, when I tried building out the solution, I couldn’t quite figure it out. Here’s a quick graphic showing the data question at hand:

Breaking down the problem of checking if cell value contains text from another list

Seems simple, right? I have a bunch of sentences in column A and a bunch of words in column C. I want to know if the sentences in column A contain any of the words in column C.

I faced this task at work a few weeks ago and the kicker was not only did I need to figure out if the sentence contained the word, but also return another column from that “List to check” (imagine another list of values in column D in the screenshot above that I want to return). Immediately, I thought about different permutations of VLOOKUP and SEARCH but quickly realized on their own or combined, these formulas wouldn’t to the trick.

If we break down the problem, what we really need to do is this:

1. Loop through each color in column C
2. Check to see if cell A2 contains any of the words in step #1
3. If it does, great! Return a TRUE or another column from the lookup list
4. Move to cell A3 and loop through all the colors again

Once we think through the steps involved, it starts to become a trickier problem. Again, I thought this problem would have a simple solution. It kept on nagging at me so I figured I should share the solution I came up with in this episode. The solution I’m showing below is in Google Sheets and the formulas are slightly different (actually easier) in Excel. Here’s the Google Sheet with the 3 methods I came up with after doing some research. This episode is also a YouTube tutorial if you prefer seeing the solution:

Method 1: Check if cell value contains text from list (partial match) and return TRUE or FALSE

This first method is actually the most important method to understand because it’s building block for methods #2 and #3 where we want to return another column from the lookup list. This is the actual dummy data from the Google Sheet we’ll be using to build out the formula for method #1:

The ten sentences all contain the first name of a character from the TV show The Fresh Prince of Bel Air (one of the all-time greatest TV shows, of course). There are a list of first names of each character in column F. Some sentences do not contain a character from the list in column F. In column B, the goal is to write a formula that returns a TRUE or FALSE if the sentence in column A contains one of the first names in cells F2:F8. If you want to jump straight to the answer, the formula you write in cell B2 is this:

If you’re using Excel (in Office 365), you can basically take out the ARRAYFORMULA in the formula or using CTRL+SHIFT+ENTER to enter the formula (if not using Office 365):

Utilizing the SEARCH function to find the name

To reiterate, this explanation only pertains to Google Sheets. In Excel, you have dynamic array formulas which change the behavior of functio