Vlookup multiple values that contain a string then concatenate values

[SarahEW]

Hi,

I am using Microsoft Excel 2016. I came across this thread which is able to do this for exact string matches.

Vlookup multiple Values then Concatenate all values in One cell

Hi Everyone, Excel Noob here, Here's my situation, most of the time I organize countries by their region to cut time, i hoping that Excel Vlookup would help me out, but as other noob like me experience is that it only throws the first value it gets and disregards the rest. For instance, Asia...

www.mrexcel.com

I tried to adapt this so that it looks for a cell that contains a string, by for example using 'string&"*"' as my search criteria, which then returns a blank cell.

=LookUpConcat(A12,'Sheet1'!A:A,'Sheet1'!A:A,",")

Is there a way to adapt this to be able to look for a string fragment?

Thanks!

 

[ExcelGzh]

If Cells A1 to A4 contain Horse, Sheep, Cow, Donkey. then

VBA
Application.Match("She*", Range("A:A"), 0)

or cell formula
Match("She*", A:A, 0)

will return 2, the row containing "Sheep"