I'm using Excel 2013.
I have an Excel table named
SiteCategories, with a number of Category columns, and then sites in those columns, like so:
So we could get to those values using
I also have a list of URLs -- think full URLs such as
What I'd like to do
For that giant list of URLs, I'd like to have columns next to them, saying whether they're in category A or category B, like so:
I'd like the matching to be case-insensitive because the URLs aren't normalized. However, if it makes it easier, I can process the fields or wrap them in
lower() though I imagine this process would be able to do that too.
Where I'm stuck
I can do matches and searches on one piece of text, but I'm having trouble doing so on a range of text.
Essentially what I want to say is, if any piece of text in the given category column is found within the URL (in a case-insensitive fashion), then the URL is in that category.
However, most of the
SEARCH functions don't appear to easily support querying a range of values.
It seems like I want to do the opposite of this question -- whereas that solution uses a wildcard on one term to find it in multiple cells, I want to use multiple cell text as wildcards against one string and return true if any of them are true.
How I'm doing it now
Currently, the formula I'm using is an
OR statement are using a bunch of
SEARCH formulas, like below (where
F3 is the cell with the URL):
=OR(ISNUMBER(SEARCH("sitea.com",F3)), ISNUMBER(SEARCH("siteb.com",F3)),ISNUMBER(SEARCH("sitec.com",F3)), ISNUMBER(SEARCH("sited.com",F3)),ISNUMBER(SEARCH("sitee.com",F3)))
However, this is a little untenable as the requirements for what sites go in what categories change over time. I'd like to be able to put those into a table to make them easily editable, hence the question.