How to do a Vlookup Using Text

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.

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

=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.

See also  How to use CORREL function

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.