Take the 2-minute tour ×

I have tried finding this solution on the web but have not had success for this specific problem. In Excel 2010 I have some data in column A where each value may partially contain data in column B.

Column A might contain "http://google.com/webmasters"
Column B might contain "google.com"
This should give me a match.

I want to print in Column C all values in column A that do not contain any values from column B.

Column A


Column B


Column C (results required)


Any help would be greatly appreciated.

share|improve this question
Do you just want an elegant solution? You could easily do this using a loop within a loop –  CAPSLOCK Jan 23 '13 at 16:49
add comment

2 Answers

up vote 0 down vote accepted

I think I have the solution using ARRAY formula. Assuming your input AND that columns A-C have titles, or simply, strings are listed starting cells A2 and B2, do the following:

  1. C2: type the formula =IF(OR(NOT(ISERROR(SEARCH(INDIRECT("B2:B"&(COUNTA($B:$B))),$A2)))),"",$A2) but press CTRL+SHIFT+ENTER instead of usual ENTER - this will define an ARRAY formula and will result in {} brackets around it (but do NOT type them manually!).
  2. Autofill formula in C2 until the end of list in column A, e.g. if the last value is in A100, then autofill up to C100 (how long column B does not matter here).
  3. You may then copy & paste obtained results as values and sort out empty strings.

Here you go! The key here - we check every string in column A for having at least one match among array of strings in column B, and return empty string in case at least one match found.

For your convenience sample file is shared: https://www.dropbox.com/s/janf0xxon4z2yh5/DomainsLookup.xlsx

share|improve this answer
Or for point 3 you may just apply filter to column C and sort out <blanks>. –  Peter L. Jan 23 '13 at 17:24
Hey great job it appears to have worked I have an extremely large data set (almost 6k in column A and 170 in column B) but as far as I can tell it did exactly what I needed thanks! –  Rudy Garcia Jan 23 '13 at 19:01
@RudyGarcia Happy to help) Please get back in case you face any troubles - my solution is based entirely on your input sample data pattern. –  Peter L. Jan 23 '13 at 19:04
add comment

Maybe not the must efficient but you could simply use two arrays - one for Column A and one for Column B. Iterate through ColumnA array to see if it exists in ColumnB array (use Array.IndexOf or .contains). If it does you could remove it from the ColumnA array and output the remaining values in Column C as the remainder.

share|improve this answer
add comment

Your Answer


