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.