Mr.Excel had a "challenge of the month" June/July 2008 about Wildcard VLOOKUP:
"The data in column A contains a series of phrases. Every phrase contains one color in the phrase. The goal of the challenge is to use the lookup table in D2:E10 to assign the phrase to one of the names in column E."
The winner created this formula to return last match in the range:
The third winner used circular reference and iterations to return multiple matches.
How to return multiple matches without circular references
The COUNTIF function counts values based on a condition or criteria, in this case two asterisks are appended to the second argument. This makes the COUNTIF function count any value that contains the string, asterisk matches zero or more characters.
The IF function has three arguments, the first one must be a logical expression. If the expression evaluates to TRUE then one thing happens (argument 2) and if FALSE another thing happens (argument 3).
Step 3 - Return the k-th smallest row number in this data set
To be able to return a new value in a cell each I use the SMALL function to filter column numbers from smallest to largest. SMALL( array, k) The second argument changes when the cell is copied to cells below, this will extract a new value in each cell.