Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

Suppose I have two columns of data. One is a list of names and the other is a list of peoples' names mixed with other stuff.

For instance, one list has "Bob" while the other might have "blank blank Bob blank blank" amongst other cells. Next row would be "Alice" and in the other "Alice blank blank". (assume they aren't sorted in any way"

What would be a formula to check if a cell in the first column is present (a substring) of any cell in the second column?

share|improve this question

migrated from Mar 16 '13 at 6:31

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

Check out the FIND and SEARCH functions! – Peter Albert Mar 15 '13 at 19:37
up vote 3 down vote accepted

You can use COUNTIF with a wildcard, e.g. if "Bob" is in A1 then you can check whether that exists somewhere in B1:B10 with this formula


that formula will return TRUE if A1 exists anywhere in B1:B10 - it's not case-sensitive

share|improve this answer
That's how you do that! I had no idea the ""&A1&"" existed. Thank you! – Astephen2 Mar 16 '13 at 1:14

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .