Find a match of multiple terms

[bongkph]

Hi,

Can you please help me with formula or function to find multiple terms and leave me with a value of those who doesn't have a match. Here is what I want to do:

Column A
abc.com/contents100
abc.com/contents200
abc.com/contents300
xyz.com/contents321
123.com/contentsaaa
123.com/contentsbbb
123.com/contentsccc

Column B (find these terms in column A)
abc.com
123.com

Column C (show all with no match)
xyz.com/contents321

I don't know if this is possible. If not, any recommendations that can give me needed results is highly appreciated.

Thanks you very much.

 

[Marcelo Branco]

Welcome to Mr Excel forum

See if this example helps

	A​	B​	C​
1​	Values​	Search​	Result​
2​	abc.com/contents100​	abc.com​	xyz.com/contents321​
3​	abc.com/contents200​	123.com​	1123.com/contentsccc​
4​	abc.com/contents300​		aabc.com/contents100​
5​	xyz.com/contents321​
6​	123.com/contentsaaa​
7​	123.com/contentsbbb​
8​	123.com/contentsccc​
9​	1123.com/contentsccc​
10​	aabc.com/contents100​

Array formula in C2 copied down
=IFERROR(INDEX(A$2:A$10,SMALL(IF(MMULT(--ISNUMBER(SEARCH(","&TRANSPOSE(B$2:B$3)&"/",","&A$2:A$10)),ROW(B$2:B$3)^0)=0,ROW(A$2:A$10)-ROW(A$2)+1),ROWS(C$2:C2))),"")
confirmed with Ctrl+Shift+Enter, not just Enter

M.

 

[bongkph]

Welcome to Mr Excel forum

Array formula in C2 copied down
=IFERROR(INDEX(A$2:A$10,SMALL(IF(MMULT(--ISNUMBER(SEARCH(","&TRANSPOSE(B$2:B$3)&"/",","&A$2:A$10)),ROW(B$2:B$3)^0)=0,ROW(A$2:A$10)-ROW(A$2)+1),ROWS(C$2:C2))),"")
confirmed with Ctrl+Shift+Enter, not just Enter
M.

Marcelo, thank you very much for the response. The actual sheet have 2552 rows in A, 309 rows in B. I edited your formula to be:
=IFERROR(INDEX(A$2:A$2552,SMALL(IF(MMULT(--ISNUMBER(SEARCH(","&TRANSPOSE(B$2:B$309)&"/",","&A$2:A$2552)),ROW(B$2:B$309)^0)=0,ROW(A$2:A$2552)-ROW(A$2)+1),ROWS(C$2:C2))),"")

This is just to confirm if I am doing it right because it takes long to reveal the values on C with given long lists of rows.

 

[bongkph]

Additionally, the actual values on Column A has preceding characters. Since it is a domain there would be http, https, subdomain (abc.abc.com), etc... before the abc.com. I would like to find the match of the actual domain (abc.com) regardless of any preceding characters. Thanks again.

 

[Marcelo Branco]

This is just to confirm if I am doing it right because it takes long to reveal the values on C with given long lists of rows.

Maybe a macro performs better.

Additionally, the actual values on Column A has preceding characters. Since it is a domain there would be http, https, subdomain (abc.abc.com), etc... before the abc.com. I would like to find the match of the actual domain (abc.com) regardless of any preceding characters. Thanks again.

Try to show us your real scenario. Simplifying your case too much leads to inadequate solutions.

M.