Contents
Plain numbers and numbers-as-text (that is, numbers that only look like numbers, but are actually text) are the cause of many problems and complexities when working with data in Microsoft Excel. One such situation is using the function VPR (VLOOKUP) for search and substitution, when the source data contains these notorious numbers in text format.
Consider a classic example – substituting prices from a price list into an order table based on matching article numbers:
Like many other Excel functions and tools, VLOOKUP considers numbers as text to be not equivalent to the same numbers in their normal form, so it gives a #N/A error, signaling that the desired value was not found, although visually it seems to be there.
Let’s take a look at the various scenarios that we may encounter here and the solutions for each.
Note: Distinguishing between normal and text numbers is not always easy. Sometimes a green indicator corner appears on such cells, sometimes not. In this article, for clarity, I will align numbers-as-text to the left, and normal numbers to the right.
Option 1. Numbers-as-text in search values
Let us first assume that pseudo-numbers occasionally occur in our desired values, i.e. in the order table (range B4:B7) and are mixed with normal numeric SKUs, resulting in errors.
You can fix the situation if you force Excel to convert all the original articles forcibly by performing any mathematical operation on them, such as:
- multiplication or division by 1
- addition or subtraction 0
- double minus sign in front of the article (equivalent to double multiplication by -1)
The value of the article will not change from performing such a harmless mathematical operation, but the very fact of its execution will force Excel to perceive the article as a number. This means that VLOOKUP will find text values without problems:
Option 2. Numbers-as-text in the table where we are looking for
The reverse side of the same coin may also occur, when the pseudo-numbers are not in the order, but in the price list articles. The same logic will help here: to force the conversion of the original numerical articles into text ones, you need to perform any harmless not mathematical, but textual operation on them.
The simplest and most compact option would be to stick an empty string to the article:
The article from such gluing does not change in any way, but is forced to be converted into a text format, because. gluing is a priori possible only for text.
Option 3: Missing Leading Zeros
A special case of the previous example is a scenario when the articles in the price list are not just text, but also contain zeros at the beginning of the lines, complementing them to a given bit depth. The function will help here TEXT (TEXT), which can display any number as text according to a given template:
Option 4. Numbers-as-text mixed with numbers in both tables
Well, and for dessert, let’s consider the most “fun” case, when we have pseudo-numbers mixed with normal numbers and are found in the articles of both tables.
It sounds scary, but it’s easy to solve – you just need to combine the first and second methods by nesting them in the IFERROR function. This function will pump both versions of the VLOOKUP – “text” and “numeric” – and return the one that does not lead to the #N/A error:
That’s it – and no more errors 🙂
- How to perform a left VLOOKUP
- Reusable VLOOKUP that retrieves all values at once
- LOOKUP function as successor to VLOOKUP