Formula to pick up if partial text is contained in a list of entries

[tigerzen]

Book1
	A	B	C	D
1	List 1	On List		List 2
2	Pete	Yes		Peter Smith
3	Sam	Yes		Samuel
4	Jack			Noah
5	Tom			Karen Brown
6	Fred	Yes		Herbert
7				Frederick Haynes
Sheet1

Looking for a formula to return if a match is found from List 2. The desired result is contained in Column B, I tried COUNTIF but unsuccessful.

 

[DanteAmor]

Hi @tigerzen , thanks for posting on the forum.

The desired result is contained in Column B, I tried COUNTIF

I show you 2 options:

Dante Amor
	A	B	C	D
1	List 1	On List	On List	List 2
2	Pete	YES	YES	Peter Smith
3	Sam	YES	YES	Samuel
4	Jack			Noah
5	Tom			Karen Brown
6	Fred	YES	YES	Herbert
7				Frederick Haynes
8
9
10
Amor

Cell Formulas
Range	Formula
B2:B6	B2	=IF(COUNTIF(D:D,"*"&A2&"*"),"YES","")
C2:C6	C2	=IF(ISNUMBER(MATCH("*" & A2 & "*",D:D,0)),"YES","")

--------------
Let me know the result and I'll get back to you as soon as I can.
Sincerely
Dante Amor
--------------

 

[tigerzen]

Hi @tigerzen , thanks for posting on the forum.



I show you 2 options:

Dante Amor
	A	B	C	D
1	List 1	On List	On List	List 2
2	Pete	YES	YES	Peter Smith
3	Sam	YES	YES	Samuel
4	Jack			Noah
5	Tom			Karen Brown
6	Fred	YES	YES	Herbert
7				Frederick Haynes
8
9
10
Amor

Cell Formulas
Range	Formula
B2:B6	B2	=IF(COUNTIF(D:D,"*"&A2&"*"),"YES","")
C2:C6	C2	=IF(ISNUMBER(MATCH("*" & A2 & "*",D:D,0)),"YES","")

--------------
Let me know the result and I'll get back to you as soon as I can.
Sincerely
Dante Amor
--------------

Thank you Dante, my Countif formula was very similar to yours but didn't quite get there. Appreciate the second solution too.