VLOOKUP with Text

One of the basic criteria for working with VLOOKUPVLOOKUPThe VLOOKUP excel function searches for a particular value and returns a corresponding match based on a unique identifier. A unique identifier is uniquely associated with all the records of the database. For instance, employee ID, student roll number, customer contact number, seller email address, etc., are unique identifiers. read more is that the lookup value should be the same in the result cell and the main data table. However, sometimes even though the lookup value looks the same in either cell, we still get an error value as #N/A! Because the format of the VLOOKUP value must be different in either cell. So, in this article, we will show you how to work with the text format of the VLOOKUP value.

Example of VLOOKUP for Text

Below is an example of Excel VLOOKUP for text.

Sometimes numbers are stored as text values, and in such cases, we cannot treat them as numbers because of the functionality of Excel. For example, look at the below data.

In the above data, everything looks like numbers. But when we sum it, we should get a total value of 3712054, but when we apply the SUM Excel function, we get the below number.

The reason behind this is that some numbers are stored as text values. So, how do we identify text values?

We can identify text values by using the ISNUMBER excel functionISNUMBER Excel FunctionISNUMBER function in excel is an information function that checks if the referred cell value is numeric or non-numeric.read more. The ISNUMBER function will return “TRUE” if the selected cell value is a number. Else, it will return “FALSE.”

So in cells B5 and B6, we got the result “FALSE,” meaning A5 and A6 cell numbers are stored as text values.

VLOOKUP Requires Exact Number Format

For example, look at the below data to apply the VLOOKUP function.

From Table 1, we need to use the VLOOKUP function to get the revenue column to result in Table 2.

So, apply the VLOOKUP function in Table 2.

We get the following result.

The result of the VLOOKUP function in cells E6 and E7 shows #N/A! Error.

Let us look at the lookup values.

It usually happens in numbers of lookup values. The main reason should be the format of the numbers in both the tables is not the same. So in such cases, we need to identify which table numbers are stored as text.

Apply the ISNUMBER function to identify non-numerical values.

As we can see, ISNUMBER identified a non-numerical value in Table 2.

When the main table data is correct and the result table numbers are stored as text, we need to convert the text formatted numbers to numerical values first and then apply VLOOKUP. There are several ways we can do this. Below are the methods.

Method 1: Convert text formatted numbers to numerical values through Paste Special

First, enter number 1 in any of the cells in the worksheet and copy that cell.

Next, select outlet ID values in Table 2 and open the Paste SpecialPaste SpecialPaste special in Excel allows you to paste partial aspects of the data copied. There are several ways to paste special in Excel, including right-clicking on the target cell and selecting paste special, or using a shortcut such as CTRL+ALT+V or ALT+E+S.read more dialogue box.

To open the Paste Special dialog box, press “ALT + E + S.”

We get the following dialogue box.

In the Paste Special window, select “Multiply” as the option.

Press “OK.” It will convert all the text formatted numbers to numerical values, and VLOOKUP now automatically fetches the data from Table 1.

The VALUE function is used to convert text formatted numbers to numerical values. As we can see, our VLOOKUP did not fetch the data because of the format of the lookup value.

To overcome this issue, which applies the lookup function, we need to enclose the VALUE function.

Look at the above formula. We have enclosed the lookup function with the VALUE function. Since we have applied the VALUE function inside the VLOOKUP function, it will first convert the non-numerical values to numerical values, then VLOOKUP treats them as numerical values only.

Method 3: What if Numbers are Stored as Text in Main Table

We have seen how to convert text values to numbers in the result table, but his numbers are stored as text values in the main table.

As shown in the above image in the main table (Table 1), values are stored as text. Therefore, in such cases, we need to enclose the TEXT function for the lookup value in the VLOOKUP function.

The problem is that the TEXT function converts even the numerical values to text values, so some of the values stored as numerical values will not work in this function. For this, we need to enclose the IFERROR condition in excelIFERROR Condition In ExcelThe IFERROR function in Excel checks a formula (or a cell) for errors and returns a specified value in place of the error.read more.

IFERROR condition tests whether the lookup value is numerical or not. If it is numerical, we will apply normal LOOKUP, or else we will use the TEXT function in excelTEXT Function In ExcelTEXT function in excel is a string function used to change a given input to the text provided in a specified number format. It is used when we large data sets from multiple users and the formats are different.read more.

So like this, we need extensive knowledge about formulas to work with VLOOKUP at the advanced level and in different scenarios.

Things to Remember

  • The TRIM function also converts text values to numerical values.First, we must test which table numbers are stored as text.ISNUMBER returns “TRUE” if the selected cell value is a number. Else, it will return “FALSE.”

This article is a guide to VLOOKUP for Text. Here, we discuss using VLOOKUP for text, examples, and a downloadable Excel template. You may learn more about Excel from the following articles: –

  • Use VLOOKUP for Different SheetsUse VLOOKUP For Different SheetsVLOOKUP facilitates the users to fetch data from different worksheets by selecting the table array from the respective worksheet of the result looking column. The lookup value must be to the left of the required result column.read moreHow to Use Names in VLOOKUP?How to do Vlookup to the Left?