Check if cell contains data in another range

[shep23]

Hi,

I need to check a bunch of domains on a daily basis that cold be suspicious (the company I work for checks tenants, and sometimes they attempt to defraud us by setting up a domain for a bogus employer)

To save time I don't want to check certain pre-approved domains, eg police.uk, gov.uk - the problem I have is that the email domain sometimes comes to me as a subdomain, for example, london.police.uk, not just the top-level domain of police.uk

So on sheet 1 I could have a list of domains like this....

metoffice.gov.uk
charliesofficesupplies.com
london.police.uk

And on sheet 2 i have
police.uk
gov.uk

How do I return if a value on sheet 1 contains the domain name in the range on sheet 2 ? So a YES for domains 1 & 3 and a no for domain 2 ?

I've tried using a wildcard vlookup, without success.

Thanks in advance,
Sheps

 

[Tubal]

Sheps,

Sheet 1 data starts in Column A1
Sheet 2 data I put in a named range "nr_GoodDomain"

I will "build" up to the answer

Get the number of "."

B1 =LEN(A1)-LEN(SUBSTITUTE(A1,".",""))

Substitute the second to last "." If there is only one ".", it will cause an error. Handel that at the end. The domain typically will not have the pipe "|", so make that the character to find.

C1 =SUBSTITUTE(A1,".","|",B1-1)

Get the text after "|".

D1  =MID(C1,FIND("|",C1)+1,99)

Combine

E1 =TRIM(IFERROR(MID(SUBSTITUTE(A1,".","|",LEN(A1)-LEN(SUBSTITUTE(A1,".",""))-1),FIND("|",SUBSTITUTE(A1,".","|",LEN(A1)-LEN(SUBSTITUTE(A1,".",""))-1))+1,99),A1))

Finally make the match to the good domains

F1  =IF(ISERROR(VLOOKUP(E1,nr_GoodDomain,1,FALSE)),"NO","YES")

Tubal

 

[shep23]

Tubal - you are a hero !

Works like a charm - I'm in the UK so a few of my domains are @example.uk.com , @example.co.uk etc which the formula couldn't account for, but I've worked around this with a nested IF and it's perfect.

Thanks again,

Sheps