Microsoft Excel – Identify Cells in Column A Containing Values from Column B

microsoft excelmicrosoft-excel-2007worksheet-function

First off, I'm not that advanced of an Excel user. I'm certain Excel can do what I'm about to ask, but I've yet to find a way in any of my attempts or searches.

My question is very similar to https://stackoverflow.com/questions/11315551/find-if-value-in-column-a-contains-value-from-column-b except that A) the answers there didn't work for me, and B) even if it did, it's not quite the correct output I need.

Basically, I have 2 columns. Column A contains URLs (16388 rows). Column B contains URL slugs (202 rows).

I want to search to see if any of the values in column B are contained within any of the values of Column A. If they are, I want to return the value TRUE in column C.

From what I've read, VLOOKUP ought to work for this, but for the life of me I can't get it to work. I also tried in the answers in the link above that use COUNTIF and MATCH, but in both of those cases, all I got were FALSE returns, when I know that there are values in Column B that are contained in a URL in Column A.

Does that make sense?

EDIT: Both answers are providing results (thank you!), but I suspect I may not have asked the question correctly. In both of the answers, I'm getting TRUE results, but it looks like the TRUE is correlating to the slug in Column B rather than the URL in Column A. Let me put it this way:

I'll move the slugs from Column B to C so that B can be used for the TRUE responses. What I want the fomula to do is:

  1. Look at URL in A1
  2. Are any of the values in Column C contained within the URL in A1?
  3. If yes, then return TRUE in B1. If FALSE, leave it blank.

So I will be able to see which of the URLs contain the slugs I am searching for. I apologize for not being clearer in my original ask (though I feel like it's only a slight tweak from the answers I've already been given.)

Best Answer

Instead of vlookup use match when looking for exact values, or `. Try this in cell C1:

=Match(B1,A:A,0)

If found this'll return the row number found, if B was not n A, then it'll return an error.

So then you can take it a step further and say this:

=If(Iserror(Match(B1,A:A,0)),"This B Is Not Found In Column A","This B is in Column A")
or
=If(Iserror(Match(B1,A:A,0)),"False","True")

Finally note that all of the match code above is for finding an exact match. If you want to check if B is ANYWHERE within A, you need to put * wildcards on each side of it like this:

=If(Iserror(Match("*"&B1&"*",A:A,0)),"False","True")
Related Question