VLOOKUP for Text

What Is VLOOKUP With Text?

The VLOOKUP with text is a function used to lookup data in the text format. We know that VLOOKUP is used to retrieve a value for the desired lookup value from the data table. To make sure the VLOOKUP function works correctly, the basic criteria is that the lookup value and format that we are searching for in the data table should be the same as in the data table.

However, when we deal with numerical lookup values, we may face the problem of lookup values being stored in text format and the lookup column in the data table stored in numerical format. In such cases, a regular approach of applying the VLOOKUP function won’t work, rather we must employ a different strategy for dealing with a numerical value stored as text.

Numerical Value Stored As Text

Let us understand the concept through a simple example of understanding numerical values stored as text.

For instance, look at the following data in Excel.

We have value 1 as 500 in cell A2 and value 2 as 500 in cell B2. To check if these two cell values are correct or not, let us apply the following logical formula.

In cell C2 we have applied the formula =A2=B2 to check if the value in cell A2 is equal to the value in cell B2. Since both the values are the same, the logical result is TRUE.

Similarly, let us check for the following data.

Even though the values in cells A2 and B2 are the same, the logical formula in cell C2 returns the result as FALSE i.e., both the cell values are not the same.

The very first thing we need to check in these cases is if the cell value is an exact numerical value or not.

One way we can check if the particular cell value is numeric or not is by using the ISNUMBER function in Excel. The ISNUMBER excel function will check for the value in the cell and returns TRUE if the cell value is numeric, or else it will return the result as FALSE.

Apply the ISNUMBER function in cell A3 to check the A2 cell value.

So, the value in cell A2 is a number, and the formula returned TRUE. Similarly, apply the same formula to check the value in cell B2.

So, the value in cell B2 is not a number; hence the ISNUMBER function returned the result as FALSE.

Hence, the logical formula in cell C2 returned the result as FALSE stating the values in cells A2 and B2 are not the same.

How To Use VLOOKUP To Search Text In Excel?

Now, let us take you through various ways of dealing with text values when we apply the VLOOKUP for text function in Excel.

#1 – Search For Specific Text Using Wildcards

When we apply the VLOOKUP function, we always look for the exact value in the table range. However, we will not always have the correct lookup value with us. For instance, look at the following data in Excel.

We have cricket player names and their representing country list.

In one of the cells, we have the following player name, and we need to find the country name for this player.

We have the player name “Butler” in cell D2. However, if we take a look at the table list, we have the full name “Jos Butler” and if we apply the VLOOKUP function, we will get the not available (#N/A) error.

As expected, we have got the #N/A error because the VLOOKUP function did not find the correct matching for the lookup value “Butler”.

In these cases, we can use the wildcard character to asterisk (*) to find the partial match of the lookup value.

Special Introduction Of Wildcard Asterisk (*) In Excel

ASTERISK (*): This is the common and most used wildcard character in Excel. Asterisk (*) matches any number of characters in a word.

• Ex* = This can match the words that start with the character “Ex” and ends with any number of characters i.e., Excellent, Excel, Experience, Extraordinary.
• *Ex = This can match the words that end with the character “Ex” and starts with any number of characters i.e., Codex, FedEx, Cemex, Duplex, Vertex, Complex.
• *Ex* = This can match words that contain the character “Ex”. Starting and Ending of the words don’t matter here. i.e., Text, Lexes, Lexis, Dextrose.

Combine the lookup value with an asterisk using the ampersand (&) symbol to get the partial matching result for the lookup value “Butler”.

As we can see, we have entered the asterisk (*) wildcard using double quotes on either side of the lookup value, and it has matched the remaining words for the lookup “Butler” and returned the county name as “England” which is correct.

#2 – Search If There Is Any Text Value In A Range Of Numbers

When you have a list of numerical values, the VLOOKUP function fails to retrieve the values for the numbers stored as text.

For instance, look at the following data in Excel.

We have the ID and city-wise sales values in a table.

And we have another table with only IDs to find the sales of each ID.

Let us apply the VLOOKUP for text function to retrieve sales information for the following ID.

We have got the results. However, we can see that a few IDs got N/A error.

If we look at the data table, IDs 5, 7, and 8 are the same, but the result is unavailable. This is because of the format of these numbers.

We need to search the format of the numerical-looking values in the lookup cells. Apply the ISNUMBER function to check the format of the cells E3, E5, and E6.

ISNUMBER evaluated these cell values as non-numerical values and, at the same time, apply the ISNUMBER function to check the format of these values in the data table as well.

In the data table, these IDs are, in fact, numerical values. Because lookup values are not in the same format as in the data table. To deal with these kinds of scenarios, we need to use the VALUE function.

Use The VALUE Function

Enclose the lookup value with the VALUE Excel function as shown in the following image.

Now we have got the sales information for text-formatted numerical IDs.

Here the VALUE function converts the text formatted numerical value to an actual numerical value and supplies the IDs as the numerical values to the VLOOKUP for text function.

However, the IDs are still in the text format in their respective cells. To convert them to number format permanently, we need to use the paste special method.

Use Paste Special Method To Convert Text Stored Numbers To Numerical Formats

Enter number 1 in any of the empty cells.

In cell F8, we have entered the number 1. Copy this cell and choose the result table ID column values.

Press the Paste special shortcut keys ALT + E + S to bring up the following paste special window.

From the Paste Special window, choose the Multiply option.

Click on OK, and all the text stored values will be converted to the numerical format.

Now we can apply VLOOKUP for text function without the VALUE function to retrieve the values.

#3 – Search For Numerical Lookup Value Inserted As Texts

Now let us look at the ways to deal with the scenario of numerical values being formatted or stored as text values in the main table itself.

We will use the same data from the above example here as well.

In the above image main table (A2:C11), the ID column is stored in text format. However, in the result table (E1:F6), the ID column is stored as numerical values.

Again, there is a mismatch between the data type of lookup values in the lookup table and in the result table.

We have applied the ISNUMBER function to check the data type of the ID column in the main table, and the result is FALSE i.e., numerical-looking values in the cells A2:A11 is not in numerical format.

Use TEXT Function To Convert Numerical Numbers To Text Format

In these cases, we can use the TEXT Excel function to convert the numerical formatted numerical values to text values.

Use the TEXT function to supply the lookup value to the VLOOKUP function as we did with the VALUE function.

Here, the TEXT function converts the numerical values to text values and supplies the text formatted numbers to the lookup value argument of the VLOOKUP function. Since the VLOOKUP function got the text value, it matches the same in the main table and returns the value from the result column (Sales).

Alternate Way

Apart from using the TEXT function, we can also the alternative method of combining the lookup value with an empty string (“”).

For instance, look at the following image.

When we combine the lookup value with an empty string (“”), it coerces the number formatted numerical value text value.

#4 – Search For A Specific Portion Of A Text Using Text Functions

When we work with numerical data in some cases, we may face different scenarios. For instance, look at the following data in Excel.

The above table contains the city code with city names for each city code.

We have another table like the following.

This table contains the customer’s phone number with the customer’s name. Using the customer’s phone number, we need to retrieve the city names of each customer.

The problem here is we have the full phone number of the customer, but the city is available based on the city code.

In the customer phone number, we have the first 4 digits as city code. To retrieve the city name for each customer, we need to first retrieve the first 4 digits to get the city code.

We will use the LEFT Excel function to retrieve the first 4 digits.

Here, the LEFT function retrieved the first 4 digits from the given text value.

Now combine this LEFT function to retrieve the lookup value for the VLOOKUP function.

Even though we are retrieving the lookup value as city code using the LEFT function, VLOOKUP cannot retrieve the city names.

The reason for this is, that the value returned by the LEFT function is a text value, not a numerical value, and in the city table, we have the city codes stored as a numerical value.

To deal with this, we need to combine the value returned by the LEFT function with the VALUE function.

In the above formula, the first part is, the LEFT function retrieving the first 4 digits, and then the VALUE function converts the value given by the LEFT function to a numerical value. Then, when we apply VLOOKUP text function, we will be able to retrieve the city name for the city code.

VLOOKUP Not Working With Text

If the VLOOKUP for text function is applied and the #N/A error is returned, then we need to look at the format of the lookup values.

Look at the following data in Excel.

And we have a result table like the following.

The colored cells are stored as text in the result table, but in the main table, these 2 values are in numerical format.

The non-colored cells are stored as numerical values in the result table, but the same values are stored as text values in the main table.

This is one of the strangest scenarios we may have to face. To deal with it efficiently, we need to employ two VLOOKUP functions and the IFERROR Excel function.

First, let us apply VLOOKUP with the VALUE function to convert all the text values to numerical values.

This method works when the lookup value is stored as text, but when the values are stored as text in the main table itself, this method fails to retrieve values.

For all the error value cells, we need to combine VLOOKUP with the TEXT function.

With this method, we need to find the error value cells and then apply the VLOOKUP function separately. However, this is not an efficient way of applying the formula.

Let us combine both functions in a single formula itself by using the IFERROR function.

IFERROR function waits for the result of the VLOOKUP and VALUE function; if this combination returns a #N/A error, then it will execute the second formula i.e., VLOOKUP and TEXT.

In this way, we can deal with both kinds of data types.

Important Things To Note

• The VLOOKUP function requires the lookup value data type format to be the same in the result table and the main table.
• If the format of the lookup value is not correct, then the VLOOKUP function will return the #N/A error.
• The TEXT function converts the numerical data type to text data type.
• The VALUE function converts the text-stored numbers to numerical data type.
• Double quotes (“”) represent the empty string. Whenever we combine this with the numerical value, it will convert the number format to text format.

Frequently Asked Questions (FAQs)

Download Template

This article must be helpful to understand the VLOOKUP for text, with its formula and examples. You can download the template here to use it instantly.

Recommended Articles

This has been a guide to VLOOKUP for Text. Here we learn how to use VLOOKUP() to search strings in Excel, along with examples and downloadable excel template. You can learn more from the following articles –

• VLOOKUP Partial Match
• Fixing VLOOKUP Errors
• VLOOKUP With MATCH