Vlookup to find multiple variations of a text string and return column

Megpie 

There are a number of ways of achieving this with formulas.

One is to use SEARCH to determine whether the search string is contained within the target list.  Within a table this might be performed record by record using a helper field

= ISNUMBER( SEARCH(searchString, [@targetString]) )

or it would appear slightly differently within an array calculation, where

= ISNUMBER( SEARCH(searchString, targetString) 

gives the entire column of results either using CSE or Dynamic Arrays (Office 365 insider only at present).  I often place array calculations within a named formula where they may be referenced and calculated on demand (here I might use the name 'matches?').

The dynamic array calculation would then proceed with

 = FILTER( Table1, matches? )

which returns every matching record.

With standard versions of Excel, things are a little more turgid.  I tend to define a further named array 'k' to hold record numbers.

= ROW(Table1[targetString]) - ROW(Table1[#Headers])

[the formula is so pig-ugly it needs to be hidden but, in future, SEQUENCE will do a much better job].  The formula

= IF( matches?, k )

gives the row numbers for matched records padded with FALSE for failed matches, and then SMALL reduces the list to consecutive values

= SMALL( IF( matches?, k ), k ),

which can be used by INDEX to return the required records.

A completely different strategy is to use MATCH to find the first record number and then to use INDEX to return the first cell of the remaining search field.  The second MATCH operates on the residual part of the table, and so on.