VLOOKUP text inside a string

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:

=VLOOKUP("*"&A1&"*",B1:B10,1,FALSE)

where

"*" - 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

 

Example:

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 string of text

=VLOOKUP("*"&A1&"*",B1:B10,1,FALSE) formula solves your problem.