Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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 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.)

share|improve this question

migrated from stackoverflow.com Oct 12 '12 at 6:04

This question came from our site for professional and enthusiast programmers.

3 Answers 3

up vote 2 down vote accepted

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")
share|improve this answer
    
So I modified your first answer to utilize the wildcards, so it read:=MATCH(""&B1&"",A:A,0), and that worked like a champ! It turns out I'm looking for a somewhat different output (see my edit to the original question), but I'm definitely saving this answer as well as this output is extremely useful to me. Thanks! :) –  Tim Harrison Oct 11 '12 at 19:44
    
Ah, I'm glad all the typing with my answer proved useful so that you could modify it the way you needed! –  Gimp Oct 11 '12 at 19:50
    
+1 for Match with wild cards –  chris neilsen Oct 12 '12 at 12:40

Have you tried the following formula, returning True if match is found and false otherwise?

=not(isna(match("*"&b1&"*",A:A,0)))
share|improve this answer
    
That did work, but it was the right answer to the wrong question (I've made an edit to the original post to try to clarify). Thank you for your help. It always better when the Asker is clear on what they want :) –  Tim Harrison Oct 11 '12 at 20:00

You already have an answer so I’m presuming to offer a 'solution' that will not only indicate whether or not present, but which is contained (hence does not meet your stated requirement!):

Insert blank row at the very top (adjust column labels if required). Copy contents of ColumnC and Paste Special, Transpose to D1. Delete ColumnsB:C. Key in B2 =IFERROR(SEARCH(B$1,$A2)>0,""). Copy B2, hold down Shift with Go To Special, Last cell, OK, Paste.

share|improve this answer
    
I'm not sure if I did that wrong or what, but I've tried following these steps multiple times and nothing ever happens. –  Tim Harrison Oct 11 '12 at 21:00
    
@TimHarrison Apologies, since already 'overstepping the mark' a bit I excluded any explanation. Have you got as far as having something in GT1, for example? Did you actually key in formula or copy/paste? Is there a formula of some kind now in say D:15000? PS Did not mention it before, but you can easily have your B:B column in addition with =IF(COUNTIF(C2:GV2,"TRUE")>0,"TRUE") in B1 and copy down. Are the 202 rows contiguous? –  pnuts Oct 11 '12 at 21:07
    
I copied the formula into B2. Then I held down shift, Go To Special, Last Cell, Ok, Paste. It took me to a far bottom right cell, but no formula was in any of them. I tried copying the formula to the clipboard, but then found that "Go To Special" was greyed out. –  Tim Harrison Oct 11 '12 at 21:12
    
@TimHarrison er, my bad, Copy B2 before Shift etc. Sorree! –  pnuts Oct 11 '12 at 21:22
    
Ok, that worked, but also apparently was too much for my crap PC, and I'm also pretty sure this is much more complex than I need. I'm really just after the results in the Edit of my OP. Thank you for your out of the box thinking though :) –  Tim Harrison Oct 11 '12 at 21:28

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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