Look-up functions such as VLOOKUP can be a godsend in Excel by improving spreadsheet integrity and efficiency. I’ve written about the basics of VLOOKUP previously, so in this article, I’ll focus on troubleshooting VLOOKUP instead.
I’m going to assume you’re using VLOOKUP to return an exact match because, by their nature, approximate matches are less prone to displaying the frustrating #N/A error.
Figure 1 illustrates a common situation with VLOOKUP. The formula in cell B2 is constructed correctly, and we can see that account 40100 appears within the list that we’re searching. Yet VLOOKUP returns #N/A, which means we’ll need to do some digging.
Figure 1: Data inconsistencies can result in VLOOKUP returning #N/A.
Cell A4 shows a test that you can do to confirm whether two cells are identical:
This formula compares cell A2 to cell D2. If both cells are identical, then the formula will return TRUE, but at this moment it returns FALSE. At this point, there are two likely issues:
Either cell A2 or cell D2 contains a number stored as text.
If both cells contain numbers stored as text, one or the other may have trailing spaces after the number.
Let’s first determine if both cells contain numbers or not, as shown by the formulas in cells A7 and A10, both of which use the ISNUMBER function. As you may surmise, the ISNUMBER function is used to determine whether a value in a cell is a number or not. The function returns TRUE to indicate a numeric value, or FALSE to indicate a non-numeric value. Because the formula referencing cell D2 returns FALSE, then the numbers in column D are being stored as text.
This situation commonly arises when using look-up functions such as VLOOKUP to reference data exported from an accounting program or other software platform. Some programs are designed to store certain numbers exported to Excel as text. If you’re simply sorting or filtering the data, you likely won’t notice the nuance, which becomes abundantly clear with look-up functions.
Fortunately, there’s an easy fix in this scenario, as shown in Figure 2:
Select the cells that contain number stored as text.
Choose the Data menu.
Click Text to Columns.
The Text to Columns wizard has three screens, but in this context, we don’t need to make any choices in the wizard. Simply launching the wizard and clicking finish instantly transforms numbers stored as text to numeric values. As you can see on the right side of Figure 2, that VLOOKUP now returns a value for account 40100 for the month of March, the comparison formula in cell A4 returns TRUE to signify the cells are identical, and both ISNUMBER functions return TRUE.
Figure 2: Text to Columns can instantly convert text to numeric values.
Now let’s turn our attention to Figure 3, which also has a VLOOKUP issue. In this case, we’re looking up the word Product Sales to no avail. Once again cell A5 compares the two cells, A2 and D2, and returns FALSE. Because clearly both cells contain text-based values, we won’t need to use the ISTEXT function, but instead will use the LEN function to determine the number of characters in each cell, as shown in cells A8 and A11, respectively. Cell A2 contains 13 characters, while cell D2 has 17 characters.
Sometimes stray spaces result from users inadvertently pressing the space bar when entering data, or sometimes software programs add extra spaces to the end of a text field.
Cell H2 shows the TRIM function, which we can use to eliminate the extraneous spaces. The TRIM function keeps one space between each word, but removes any other spaces. In this case, if we were to copy cells H2:H5 and use Excel’s Paste Special command to paste as values over cells D2 through D5, then VLOOKUP would return the value we’re seeking.
Another alternative to using the TRIM function to alter the original data is to incorporate a wildcard character into VLOOKUP, as shown in cell B3 of Figure 3. Instead of simply seeking the contents of cell A3, the lookup_value argument in VLOOKUP is A3&"*".
The ampersand takes the place of Excel’s CONCATENATE function that is often used for combining text together. The asterisk must be enclosed in double-quotes, but in this case, it instructs VLOOKUP to find a match based on a cell that begins with the contents of cell A3. Bear in mind that this means we’re no longer seeing a truly exact match, but if the only variation in your data is trailing spaces, then the wildcard character should help you avoid frustration.
Figure 3: The TRIM function or wildcard character can also be used if VLOOKUP is returning a #N/A error.
David H. Ringstrom, CPA, is an author and nationally recognized instructor who teaches scores of webinars each year. His Excel courses are based on over 25 years of consulting and teaching experience. His mantra is “Either you work Excel, or it works you.” David offers spreadsheet and database consulting services nationwide.