You can use your favorite social network to register or link an existing account:
Or use your email address to register without a social network:
Sign in with these social networks:
Or enter your username and password
Forgot your password?
Yes, please link my existing account with for quick, secure access.
No, I would like to create a new account with my profile information.
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!
Then, use the IFERROR route with conditional formatting. That way, say, red color fonts can still draw the eye to the error without displaying something as unreadable as #N/A.
The tool I use most often with vlookup is changing the data from text to columns. The data can be in the lookup table or it can actually be in the list you are looking at. Pull down Data/Click on Text to Columns and follow the prompt through.
You can also use the the function IsNA(); (return TRUE or FALSE) and depending on what is returned you format result in cell with a IF()
Hello, I don't think we want to eradicate #N/A. I'd let it flow, use a dummy column, and test with IsNa(). You can hide the potential-error column, and that error value is potentially important...
Sometimes a value looks like a number but vlookup doesn't agree. You can multiply the column by 1. When I show this to folks I sneak a lesson on paste-special-multiply.
Hey everyone: Thought I replied to this earlier, but my comment seems to have gone the way of the dodo. THANK YOU for the excellent tips, and please, keep them coming. I can turn them into another blog post and give you credit. Nate, point taken about not necessarily wanting to eradicate #N/A--perhaps "manage your #N/A errors" would have been a better way to phrase that, eh?
Precisely. And now you're starting to talk like me - and it's making me nervous, eh. (not really)
recently i have used if..iferror and Vlookup in nest.....for my requirement.
IF(ISNA(VLOOKUP(value;table;column_no;FALSE));0;VLOOKUP(value;table;column_no;FALSE)) This returnes either value or zero
I encountered the same problem of #n/a. Probably this applies to situations where you use vlookup funtion in the excel. If you are using ms excel 2007 then try doing this :
Go to Excel options in the windows icon on your excel.
Activate, Analysis ToolPak, Lookup Wizard, Solver-Add-In
This worked for me. After activating these i did not face the problem of #n/a anymore.
I enountered the same problem of #n/a. Probably this happens in situations where you use vlookup functions. If you are using MS Excel 2007, do the following :
Go to Excel Options from your windows icon in your excel
Activate Lookup Wizard, Solver Add-In, Analysis ToolPak
It worked for me after I activated these.
Happy Excelling.. :)
VLOOKUP is an extremely useful funtion and I use it frequently. But I need it to return the value from my array and also to change the text in an adjacent cell based on the return value. I haven't been able to find information on vlookup returning multiple values or a list value with a function, or maybe I'm searching with the wrong terms. I'm using 2007. Any thoughts or suggestions?