I call vlookup the “duct tape of Excel” — it’s a cheap, easy way to patch together two sets of data. Like with any patchwork solution, things can get out of order. How to avoid constantly correcting for #N/A, #NAME, and other Excel meltdowns?
You just need to be two steps ahead of your data. Avoid the frustration of diagnosing errors by following these practices.
1. Use a unique key Write your vlookup off a SKU, employee ID number, or other attributes that are only assigned to one person and will not change. Do not vlookup off someone’s name! Why?
-People get married, ask to use their nickname, etc. If you’re writing a vlookup off this, you will not get a match
-Names are not always unique: you can have two Bob Smiths. But each Bob will have a different ID#.
A corollary: when sending your data set to someone, always include a unique, constant identifier. The first thing I do when getting a data set is to find this identifier. It hurts when someone sends me a list of names with no identifier.
“This is so helpful, because there’s only one Jones in the entire company.”
2. Overstate your data array. When writing a vlookup, Excel will default to use your exact array field for the lookup. What if you add new data? It will not be included in your vlookup.
To avoid this, overstate your array data on purpose. In newer versions of Excel, you can even use the entire column as your source data. For example, =VLOOKUP(A2,$A$:$D$,2,FALSE)
“Column references would have avoided this.”
3. Plan for exceptions. One of the hardest parts of working with data is knowing the exclusions and exceptions. You get a spreadsheet of sales. Every store carries the product except _____. Every salesperson is on the list except ______. Getting a #N/A on a vlookup is frustrating. This is why out of habit, I always include an IFERROR clause. The vlookup works the same, you just add IFERROR at the beginning, and your specified result for an error at the end.
=IFERROR(VLOOKUP(….),”NOT ON LIST”)
IFERROR will return whatever result you specify whenever there is a formula error in Excel.
“This is so much easier to read than #N/A.”
–
Vlookup is a great tool — when things are going right. As an analyst, you still have to diagnose errors in your spreadsheet. With some forward thinking, you can avoid the most common reasons a Vlookup will stop working. This will make you more efficient so you can spend time valuable data analysis.
Leave a Reply