Scrubbing Mass Email List

[powellku]

Hi Excel Gurus,

I've been working on this problem for days and tried numerous different formula to no avail, any help is appreciated!

Problem: I have a list of 250,000 emails that needs to have all emails associated with lawyers removed (ex. info@lawofficexyz.com).

Attempted Solutions: In sheet 1, I've split all emails into their root (ex. "info") and their domain (ex."lawofficexyz") to create two additional separate columns. In sheet 2, I have a list of 2250 words that are associated with lawyers emails ranging from strings (ex. "law" which should catch "lawyer") as well as specific domains and specific emails that I need to get rid of. I've attempted to use Vlookup, Match, Index, Countif, Search, IF formulas in different combinations. Typically, the object of the function is in sheet 1 and then the array of the function is in sheet 2.

Persistent Issue: 1) Too broad, when using an approximate match or partial string, my formulas identify every email rendering the formula useless. 2) Too narrow, when using exact match my formulas miss a lot of lawyer's emails which is missing the objective.

I can't seem to crack this one. Thank you again for you help!

 

[sparky2205]

Have you tried using wildcards with your lookups? It might just be the same as an approximate match but I haven't seen your data so I thought I'd throw it out there.