VLOOKUP Text Inside A String
For this just use a vlookup with such a syntax:
“*” – some string of text
& – connection
A1 – here you have a string of text you are looking for in the array
& – again connection of formula
“*” – again some string of text
FALSE – that is kind of important, because you are looking for exact match here
You have a column of first names and family names. You want to find out what is the name of some person and you remember only the family name.
=VLOOKUP(“*”&A1&”*”,B1:B10,1,FALSE) formula solves your problem.