Searching against a partial match in excel

问题:

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:

[e-mail]     / backpacks.com 
[e-mail]              / bags.io
[e-mail]   / plasticbags.net
[e-mail]        / 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?

回答1:

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.

回答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 :)!

回答3:

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!