Searching against a partial match in excel

Question

Tab-A contains a two columns. Column 1 is website domains, column 2 is filled with a formula that should output emails found in Tab-B:

backpacks.com         / FORMULA TO FIND EMAIL FROM TAB-B 
buy.bags.io           / FORMULA TO FIND EMAIL FROM TAB-B
shop.plasticbags.net  / FORMULA TO FIND EMAIL FROM TAB-B
bags.org              / FORMULA TO FIND EMAIL FROM TAB-B

Tab-B contains two columns. Column 1 is emails, column 2 is email domains:

admin@backpacks.com     / backpacks.com 
hi@bags.io              / bags.io
hello@plasticbags.net   / plasticbags.net
support@bags.org        / bags.org   

For each domain in Tab A, I want to search through the email domains in Tab B and see if there is a partial match. For example, "bags.io" is partial match of "buy.bags.io". I extracted the email domains from the email address to match searching easier, but you could also search through the entire email address for a partial match if you know how.

If there is a match I want to output it's associated email. If there is no match I want to put a blank cell.

I've been trying to figure this out using Vlookup and others but I just can't get it. How can I get this done?

Answer 1

Well... I have a much more complicated answer than Scott. For posterity, here it is!

1. In Tab-A Column B, I created a helper column that counts the number of . in the domain
2. In Tab-A Column C & D & E, I created a formula to return each "chunk" of the domain.
3. In Tab-A Column F, I created a formula that returns the email based on two searches: combining Column C&D, and if that returns no value, then combining Column D&E.

What this does is searches for multiple combinations of the chunks of the domain rather than searching for a partial match from the email address.

In B1 =LEN(A1)-LEN(SUBSTITUTE(A1,".",""))
In C1 =IF(A1=0,"",TRIM(LEFT(RIGHT(SUBSTITUTE("."&A1,".",REPT(" ",99)),(B1+1)*99),99)))
In D1 =IF(A1=0,"",TRIM(LEFT(RIGHT(SUBSTITUTE("."&A1,".",REPT(" ",99)),(B1)*99),99)))
In E1 =IF(A1=0,"",TRIM(LEFT(RIGHT(SUBSTITUTE("."&A1,".",REPT(" ",99)),(B1-1)*99),99)))
In F1 =INDEX('Tab-B'!A1:A4,IFNA(MATCH("*"&C1&"."&D1&"*",'Tab-B'!B1:B4,0)<>"",MATCH("*"&D1&"."&E1&"*",'Tab-B'!B1:B4,0)))

Allow me to say that Scott's solution is a helluva lot more elegant!

Answer 2

If you can use VLOOKUP then you could use:

VLOOKUP("*"& lookup value  &"*" , table array , column number, FALSE)

Where "*" is wildcard.

"*"& lookup value: then it accept other words infront of your lookup value.

"*"& lookup value: then it accept other words after your lookup value.

"*"& lookup value &"*": accept words on both sides of your lookup value.

Happy lookup :)!

Answer 3

Use:

=IFERROR(INDEX(D:D,AGGREGATE(15,7,ROW($E$1:$E$4)/(ISNUMBER(SEARCH($E$1:$E$4,A1))),1)),"")

It iterates the one column with the domains and returns the partial match row to the INDEX which returns the email.