VLOOKUP for Text

VLOOKUP with Text

One of the basic criteria to work with VLOOKUP is the “lookup” value should be the same in the result cell and also in the main data table, but sometimes even though the lookup value looks the same in either cell still we end up getting an error value as #N/A!. This is 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.

You can download this VLOOKUP for Text Excel Template here – VLOOKUP for Text Excel Template

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.

VLOOKUP for Text Example 1

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.

 Example 1.1

The reason behind this is because 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. Its output is a boolean value (“True,” if the “value” parameter is numeric or “False” if the “value” parameter is non-numeric).read more. ISNUMBER function will return TRUE if the selected cell value is number or else it will return FALSE.

VLOOKUP for Text Example 1.2

So in cell B5 & B6, we got the result as FALSE that means A5 & A6 cell numbers are stored as text values.

 Example 1.3

VLOOKUP Requires Exact Number Format

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

VLOOKUP for Text Example 2

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

So, apply the VLOOKUP function in table 2.

 Example 2.0.1

We get the following result.

Example 2.2.0

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

Let’s look at the lookup values.

Example 2.3

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

Apply the ISNUMBER function to identify non-numerical values.

VLOOKUP for Text Example 2.4.1

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

When the main table data is correct and the result table numbers are stored as text, then 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 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.

Example 2.5.0

Now select outlet ID values in table 2 and open 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 paste special dialogue box press ALT + E + S.

VLOOKUP for Text Example 2.12

We get the following dialogue box.

VLOOKUP for Text Example 2.6

In the paste, a special window, select “multiply” as the option.

 Example 2.7

Press ok, it will convert all the text formatted numbers to numerical values, and VLOOKUP now automatically fetch the data from Table 1.

VLOOKUP for Text Example 2.8.0

Method 2: Convert By Using VALUE Function

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

VLOOKUP for Text Example 3

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

 Example 3.1

Look at the above formula. I 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 first, 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 what his numbers are stored as text values in the main table itself.

Example 2.9

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

VLOOKUP for Text Example 2.10.0

The problem here is TEX function converts even the numerical values to text values, and so some of the values which are stored as numerical values will not work in this function. For this, we need to enclose the IFERROR condition in excelIFERROR Condition In ExcelWhen an error occurs in any formula or argument, the IFERROR excel function is used to display the result. This function can be used in conjunction with other functions to alert the user of any errors in the formula.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 apply the TEXT function in excel.

Example 2.11.0

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

Things to Remember

  • The TRIM function also converts text values to numerical values.
  • First, we need to test in which table numbers are stored as text.
  • ISNUMBER returns TRUE if the selected cell value is number or else it will return FALSE.

Recommended Articles

This has been a guide to VLOOKUP for Text. Here we discuss how to use VLOOKUP for text along with examples and a downloadable excel template. You may learn more about excel from the following articles –

  • 35+ Courses
  • 120+ Hours
  • Full Lifetime Access
  • Certificate of Completion