In this Excel tutorial, you learn how to vlookup using string of text. This is a simple trick you will understand and use many times in Excel.
Table of Contents
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.
Combine Multiple Criteria
In many scenarios, you’ll need to use more than one criterion for your lookup. For example, you may want to find a name based on both the last name and the city. To achieve this, use the CONCATENATE or & operator to combine the criteria:
=VLOOKUP(“*”&A1&”*”&”|”&B1&”*”, C1:C10, 1, FALSE)
This formula searches for a combination of last name and city, separated by a pipe symbol (|), in the data table.
Nested Functions for Text Cleaning
In real-world datasets, text values often come with inconsistencies like extra spaces, punctuation, or formatting variations. You can nest text-cleaning functions like SUBSTITUTE, TRIM, and CLEAN within your VLOOKUP formulas to ensure accurate matches.
By mastering these advanced VLOOKUP techniques for text-based lookups, you’ll be well-equipped to handle complex data retrieval tasks, even when working with unstructured or partially known text values in Excel.
