Google sheets lookup with substring on range

Question

I am trying to do substring lookups with Google Sheets.

I am able to do it when the substring is applied on the lookup string:

Match = VLOOKUP("*"&A2&"*",$C$2:$C,1,FALSE)

However I'm stuck when it comes to applying the substring logic to the range:

Any idea how to achieve this?

Note: I've used simplistic values for the sake of the example, in reality the search/range values don't always follow the [0-9][a-z][0-9] / [a-z] pattern, so removing the digits from search then performing the search isn't the solution I'm looking for.

Answer 1

=TEXTJOIN(",",1,QUERY(C$2:C$4,"select C where '"&A2&"' contains C"))

• QUERY to find the substring match
• TEXTJOIN to join matched substring,if more than one is found.