Find string (cell reference) in column on sheet 2, and return the found string

[Knockoutpie]

Hi all, been working on this for a few days trying to figure out the best way to do it..

I'm trying to use the cell contents in A2, search for that string in Column 1 on sheet 2 and return the value, then loop down to the end.
The string value won't match exactly as shown below in the desired result.

Any help would be awesome! I'm also not against a formula if one exists that can help.
Obviously, I can Ctrl + F on every single one but that is a lot of work for 4000 rows.

Desired Results

Column with contents being searched

 

[Fluff]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

How about

=INDEX(Sheet2!A2:A100,MATCH("*"&A2&"*",Sheet2!A2:A100,0))

 

[Knockoutpie]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

How about

=INDEX(Sheet2!A2:A100,MATCH("*"&A2&"*",Sheet2!A2:A100,0))

Index Match! I was just looking at that before you replied, thank you so much!

 

[Fluff]

You're welcome & thanks for the feedback.

Please don't forget to update your account details.