Todayâ€™s blog post is brought to you by Anneliese Wirth, who writes about Excel for Office.com.
To grow your skills with Excel, itâ€™s helpful to familiarize yourself with a core set of functions, or predefined formulas that are built into Excel. One of my all-time favorites is VLOOKUP. If youâ€™re new to VLOOKUP, we have a free, entry-level training course available on Office.com that can get you started.
If youâ€™re familiar with VLOOKUP, youâ€™ve no doubt seen this before:
#N/A errors really irritate me. First, it looks like somethingâ€™s broken on my worksheet, and thatâ€™s just bad form. Second, #N/A can complicate life if youâ€™re trying to use your VLOOKUP results in other formulas.
In Excelâ€™s defense, these errors appear for a reason. Simply put, #N/A is Excelâ€™s way of telling you that the thing youâ€™re looking for doesnâ€™t exist in your lookup table. Yes, it may look like thereâ€™s a perfectly wonderful match in your lookup table, but believe me, if youâ€™re seeing #N/A, the match doesnâ€™t exist (as far as the function is concerned, anyway).
With exact-match VLOOKUPs, #N/A errors often occur when:
Â· The thing Iâ€™m looking for is in my lookup table, but Excel doesnâ€™t â€œseeâ€ the match. Itâ€™s tempting to fixate on my formula when troubleshooting, but the problem often stems from bad data in the lookup table. VLOOKUP always looks in the first column of the lookup table for a match to the lookup value you specified in your formula. When youâ€™re troubleshooting #N/A errors, always focus your sights on that column. Scrub it carefully for misspellings, extra leading or trailing spaces, invisible characters and line breaks, numbers or dates that arenâ€™t formatted correctly, and so on. This is especially important if youâ€™re importing or copying data from another source, like a database or web site, because formatting oddities are common and can be hard to spot. You may have to dig for them by using TRIM, CLEAN, and other helper functions. (Donâ€™t panic about that last part; itâ€™s not hard, as Mike Girvin demonstrates in this informative video.)
Â· The thing Iâ€™m looking up really isnâ€™t in my lookup tableâ€”for example, a particular employee name is missing. In this case, #N/A is doing me a favor by drawing my attention to what doesnâ€™t actually exist. Hereâ€™s a trick: use VLOOKUP together with the IFERROR functionâ€”then, if VLOOKUP canâ€™t find something, I can tell it to show me a message such as â€œEmployee not foundâ€ instead of the confounding #N/A error.
You can learn a few simple troubleshooting techniques in this troubleshooting tips card. While the card doesnâ€™t list every scenario that may result in an error, it covers some of the main ones.
If you have your own troubleshooting tips to share, or if you have a more lucid way to explain the intricacies of the #N/A error as it relates to VLOOKUP, I would love to hear from youâ€”and so would everyone else. It takes a village to eradicate #N/A!