By default, Excel’s SEARCH function looks for one value inside of one cell. Jon Acampora from ExcelCampus (one of my favorite blogs) demonstrates how to look for various values within one cell with this formula:
=IFERROR(LOOKUP(1E+100,SEARCH(tblFilterList[Filter List],[@Name]),tblFilterList[Filter List]),””)
How Does It Work?
Why Do I Like His Formula?
Normally, when using the SEARCH function in this manner (creating an array of search values) it would be an array formula (CONTROL SHIFT ENTER instead of just ENTER).
In this case we simply press ENTER because the LOOKUP function was designed to handle an array of values. This is why I like Jon’s formula. It reminded me that some functions can handle arrays without CONTROL SHIFT ENTER. Note: INDEX, SUMPRODUCT and AGGREGATE functions can also handle arrays without CONTROL SHIFT ENTER.
How Can We Count Search Words Found?
This comment was made on Jon’s YouTube video:
Exceptional as always. Quick question though: What if we had Beachwood City, how would we be able to count all three of them, please?
It’s an interesting question and I came up with this array formula:
This formula does require CONTROL SHIFT ENTER. Note: if a search word is found multiple times in a cell it counts as only 1.
When It WON’T work!
Jon’s LOOKUP & SEARCH solution works perfectly if only one search word is found within the city names. If multiple words are found then, due to the nature of the LOOKUP function’s approximate match, the last search word found is what would appear in column ‘Partial Match Filter’.
Example: In cell A344 we find ‘Redwood City’. This contains search words ‘wood’ and ‘City’ but the last search word ‘wood’ appears in cell C344.
If you want to prioritize the words then I would suggest changing the word order in field ‘Filter List’.
Just One More Thing….
Remember Columbo? His famous line was “Just One more thing…” often followed by a question.
Here’s my “One more thing”:
What if we want to count all the occurrences of each individual search word even if it occurs multiple times in the same cell?
In column F of sheet ‘Count all occurrences of word‘ I used this array beast:
=(SUM((LEN(CA_Cities___Wikipedia[Name])-LEN(SUBSTITUTE(LOWER(CA_Cities___Wikipedia[Name]),LOWER([@[Filter List]]),””)))))/LEN([@[Filter List]])
- Calculate length of each ‘Name’ text LEN(CA_Cities___Wikipedia[Name])-
- Calculate length of each ‘Name’ text after substituting search word with a blank LEN(SUBSTITUTE(LOWER(CA_Cities___Wikipedia[Name]),LOWER([@[Filter List]]),””))
- Step 1 subtract step 2. Take these results and divide by search word length /LEN([@[Filter List]])
There’s probably a shorter way to solve this in a single cell. Can you think of a way?
We could of course use a non array formula as a helper column for each unique search word. In many ways this would be a better solution as it would be less complex to understand.
My name is Kevin Lehrbass. I live in Markham, Ontario, Canada. I’ve been studying, supporting, building, troubleshooting, teaching and dreaming in Excel since 2001. I’m a Data Analyst at McKinsey & Company.
Away from Excel I enjoy playing with my dogs Cali & Fenton, learning Spanish, playing Chess and reading Excel books 🙂