Excel FILTER FUNCTION contains text

Don't even visit my website just copy this LOL -> `ISNUMBER(SEARCH("Special Sales Job", sales[project_name])`

me@jaykilleen.com wrote this about 1 year ago and it was last updated about 1 year ago.


← Back to the Posts

ISNUMBER(SEARCH("Special Sales Job", sales[project_name]))

Gone are the days of INDEX/MATCH or SUMIFS to try to pluck that one value out of a table based on a set of conditions.

More and more I find myself reaching for combination of INDEX FILTER UNIQUE and SORT to ensure I am returning some unique id from a table column.

But searching where a field might contain some text always stumps me. Using the wildcard * works great in a SUMIFS but is not supported in a FILTER FUNCTION (so annoying amirite?!).

So here is what I am using in that place... hard to remember... so here is a simple Google fu search you can do to quickly find this template :O

Just bust open Google, search excel filter function contains text jaykilleen and you are teleported directly to this result... I even put it in the snippet of the search results so you don't even have to come to my website LOL

FILTER(sales[customer_id], ISNUMBER(SEARCH("Special Sales Job", sales[project_name])), "No results")

or without the FILTER stuff so you are just getting the bit that stumps ya!
I even put it at the top of this post so you get it straight away unlike a recipe website where you scroll until you are starving to get what you came for XD

ISNUMBER(SEARCH("Special Sales Job", sales[project_name]))