Skip to main content
Skip to main content
Microsoft 365

VLOOKUP: No more #N/A



Today’s blog post is brought to you by Anneliese Wirth, who writes about Excel for



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 that can get you started.

Excel icon

Microsoft Excel

Turn data into insights.

Get Excel

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!

Related posts

Image for: View live transcripts in Microsoft Teams meetings, track Excel changes, and increase hybrid work security—here’s what’s new to Microsoft 365
• 5 min read

View live transcripts in Microsoft Teams meetings, track Excel changes, and increase hybrid work security—here’s what’s new to Microsoft 365

This month, we're announcing some top requested features like tracking changes in Excel and live transcriptions in Teams, as well as some soon-to-be-favorites like meeting recaps, turning Word documents in presentations, and more.

Image for: Family hanging out at home baking holiday cookies using Lenovo Ideapad Flex 3 to make holiday cards
• 5 min read

New apps and features in Microsoft 365 for individuals and families this fall

Since we introduced Microsoft 365 to individuals and families earlier this year, we have continued to deliver new innovations across our apps and services to help you and your family save time and stay connected. See what’s new below. Locate your loved ones and build better driving habits with Microsoft Family Safety Stay connected even…

Image for: A man is using his Lenovo laptop like a tablet while sitting in a comfortable chair in a Modern office setting
• 6 min read

Microsoft Productivity Score and personalized experiences—here’s what’s new to Microsoft 365 in October

As I reflect on an action-packed few weeks, I’m struck by how much work has evolved in these past months. And I know our customers feel it too. After quickly moving to remote and hybrid work models this spring, organizations are now seeking sustainable ways to help people collaborate, be productive, and prioritize their wellbeing…