In this lesson you will learn how to vlookup using string of text. This is simple trick you will understand and use many times in Excel.
You can use vlookup to lookup 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.
Further reading: How to Remove N/A! Errors in VLOOKUP Formula? How to create many to many lookup? Backwards Vlookup