Introduction
Imagine you have been given a time sensitive report request. You have an executive breathing down your neck for this data and suddenly you get the dreaded #N/A error on your VLOOKUP. This issue is more common than you think, especially when dealing with numbers formatted as text which happens often with HR data. Don’t worry; we’ve got you covered! I will walk through how you can identify this issue and provide solutions with examples on how to fix this in your data.
Understanding the Issue
Excel treats numbers and text differently. This difference can cause problems when you are using the VLOOKUP function. If the data type in your lookup column doesn’t match the data type in your lookup value, you may receive an error or incorrect result. This issue often arises when one data set has numbers stored as text, while the other has numbers stored as numbers.
Recognizing the Problem
You can easily find numbers stored as text by looking for a green triangle in the upper-left corner of the cell. This triangle is Excel’s way of showing that the number stored in the cell is formatted as text.
Let’s look at this through the test data we have used in previous posts. I have added a column for Social Security Number with the numbers stored as text (Note these are not real SSNs). This is a frequent problem with Social Security Numbers as they sometimes start with “0” and methods to show the “0” require converting the numbers to text. You can download the updated spreadsheet below (It also has a tab called Matched Emails where you can practice the different methods of fixing the issue).
In the screenshot, you can see the green triangles. Clicking in the cell and selecting the yellow warning diamond shows you the error message.
If you were trying to VLOOKUP against a list of SSNs in numerical format and were, you would get the #N/A error as shown below since the source data is in ‘Text’ format and the lookup data is in ‘Numerical’ format:
What can cause numbers to be stored as text
- Manual Text Formatting: If the user manually changes the cell format to “Text” before entering the number, Excel will treat the entry as text.
- Imported Data: If the data is imported from another source (like a text file, a database, or another spreadsheet), numbers might be treated as text if the original data source had them formatted as such.
- Use of Apostrophe: If a number is preceded by an apostrophe (‘), Excel treats the number as text. This method is often used when the user wants to preserve leading zeros in a number. For example, entering ‘00123 in a cell will display 00123, not 123. This is
- Use of Non-Numeric Characters: If a cell has any non-numeric characters (including spaces), Excel will treat the cell’s contents as text. For instance, entering 123-45 or 123 45 will cause Excel to treat these entries as text.
- Errors in Formulas: Sometimes, when a formula is used to generate numbers and there’s an error in the formula, Excel might output the result as text.
- Formulas Returning Text: Certain Excel functions return text instead of numbers, even when dealing with numeric data. For example, the CONCATENATE function or & operator used to join text and numbers together will result in text.
Solutions
There are three easy methods to resolve this issue. Note that these should work for versions of Excel 2007 and later. If you are using an Excel 2003 or earlier it is recommended to upgrade for better functionality, features, and support.:
Solution 1: Convert Text to Numbers:
1. Select the cells that need to be converted.
2. Click on the warning symbol (an exclamation mark) that appears next to the green triangle.
3. In the drop-down menu, select ‘Convert to Number’.
Now, all the cells that were formatted as text are converted to numbers, and VLOOKUP should work correctly.
Solution 2: Convert Numbers to Text:
Alternatively, you can convert the values you are trying to match to text to match the source values. This would be useful if you didn’t want to change the format of the source data.
1. In our Matched Emails tab, create a second column. In the first cell, type the formula =TEXT(A2, “0”)
2. Copy this formula all the way down
3. Update your VLOOKUP to match in the new column
4. Copy down your updated VLOOKUP
Solution 3: Use the VALUE or TEXT function in the VLOOKUP formula:
You can also use the VALUE or TEXT function directly in your VLOOKUP formula to convert your lookup value to match your lookup column. Here’s how:
- If your lookup column is formatted as text, use the TEXT function to make the VLOOKUP match formatting with the source: =VLOOKUP(TEXT(lookup value, “0”), lookup range, column number, FALSE)
- If your lookup column is formatted as numbers, use the VALUE function: =VLOOKUP(VALUE(lookup value), lookup range, column number, FALSE)
Mixed Format Columns
You may run into situations where a column has a mixture of text and numeric data. An example I have seen with this is position codes where the organization used to use numeric values and then switched to adding a text prefix at the front to categorize certain types of positions. When extracting the data, you may see a situation like below where the original data is in mixed formats, but the extracted data is all text (which is Excel’s default when there are any text values in a column).
In this situation, you can use solution 2 above to convert the original data to all text so it is consistent with the data you are evaluating against.
Conclusion
Excel’s VLOOKUP is a powerful function for Human Resources professionals that can save you time and effort. However, when numbers formatted as text (or vice versa) come into the picture, things can get a bit tricky. By finding the issue and using the solutions provided above, you can ensure that your VLOOKUP functions are working correctly, regardless of whether you’re dealing with numbers or text. Remember, Excel is a tool, and mastering its use is all about understanding how to navigate these small challenges.
Did this post help you navigate through your VLOOKUP troubles? Are there any other Excel challenges you face in your daily work that you’d like us to cover? Let us know in the comments section. Remember, the journey to mastering Excel is an ongoing process, and we’re here to guide you through it. Don’t forget to share this post with your colleagues who might find it helpful and subscribe for more Excel tips and tricks. Happy Excel-ing!
Leave a Reply