3

Background

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:

enter image description here

So we could get to those values using =SiteCategories[Category1]

I also have a list of URLs -- think full URLs such as http://www.sitea.com/page/page.html

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:

enter image description here

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 MATCH and 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.

1

You can use an array formula entered with ctrl+shift+enter. Search is not case sensitive so that should be fine.

=OR(IFERROR(SEARCH(SiteCategories[Category1],A2),0))

Here it is setup to handle blank lines in the table thanks to Kyle in the comments.

=OR(IFERROR(IF(SiteCategories[Category2]<>"",SEARCH(SiteCategories[Category2],A‌​2),0),0))
  • NICE! I was trying an array formula but wasn't wrapping it in OR. I'm going to test this out and then will likely mark it as the answer. Thanks! I really appreciate the help and the nice concise formula. – SeanKilleen Sep 1 '15 at 19:47
  • I'm running into one issue -- it seems that when I run this for the Category2 example above, which has some blank cells, it matches everything. Is there a way that I can use a structured table reference to only pull the columns that actually have data? Or do I need to wrap that in something? – SeanKilleen Sep 1 '15 at 20:04
  • 1
    @SeanKilleen this is @gtwebb's formula, accounting for blanks =OR(IFERROR(IF(SiteCategories[Category2]<>"",SEARCH(SiteCategories[Category2],A2),0),0)) – Kyle Sep 1 '15 at 20:48

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.