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.
About David Ringstrom, CPA
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.