Lookup list of values that partially matches other array and it returns with custom value

Question

I am trying to do any partial matches in the column G with the values from column A. If any of the values from column A matches in column G, the return values is yes.

I tried many different lookup functions, but still can't figure it out. Can someone help me?

Tried Vlookup, Xlookup, Index Match.

Answer 1

Use XMATCH() with wild card matching option. Try-

=ISNUMBER(XMATCH("*"&A2&"*",$C$2:$C$6,2))

COUNTIFS() will also work.

=IF(COUNTIFS($C$2:$C$6,"*"&A2&"*")>0,"Yes","NO")

Answer 2

Stack Column and Matches

=LET(a,A2:A6,b,C2:C6,t,"Yes",f,"No",
     HSTACK(a,IF(ISNUMBER(XMATCH("*"&a&"*",b,2)),t,f)))

Results Only

=IF(ISNUMBER(XMATCH("*"&A2:A6&"*",C2:C6,2)),"Yes","No")