In this Excel tutorial, you will learn how to vlookup using string of text. This is a simple trick you will understand and use many times in Excel.
Vlookup text formula
In Microsoft Excel, the VLOOKUP function can be used to search for a specific value in a table of data and return a related value from the same row. When using the VLOOKUP function with text values, you need to ensure that the data in the table is correctly formatted, and that the search criteria is correctly specified. 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
Vlookup text example
Here is an example of Vlookup used with text in Excel.
You have a column of first names and family names. You want to find out the name of some person, but you only remember their family name.
=VLOOKUP("*"&A1&"*",B1:B10,1,FALSE) formula solves your problem.
Note: When using text values with the VLOOKUP function, it is important to ensure that the text values in the table of data are correctly formatted. For example, if some of the text values in the table are uppercase and some are lowercase, the VLOOKUP function may not find a match if the search criteria is in a different case. To avoid this issue, you can use the UPPER or LOWER functions to convert all the text values in the table to uppercase or lowercase, respectively.
Further reading: How to create many to many lookup? Backwards Vlookup