Common Error Types
Lets have a look at the possible error messages you may get when using VLOOKUP, what they mean and how we can deal with them.
|Error||What This Means|
|#N/A||You’ll see this error message when the lookup_value is not found in the table_range you have specified|
|#NAME?||You probably misspelled an Excel function (either a typo or there’s an erroneous space playing hide and seek in your formula)|
|#VALUE||Your col_index_num is liklely less than 1. We can only VLOOKUP left to right, starting with the first column (column 1)|
So if you see one of these messages I would suggest you first to check your data manually to see if you EXPECT a result to be returned. If you do, some troubleshooting is needed.
This error message will become your new best friend, as you’ll likely see it quite frequently when using VLOOKUP. The ‘NA’ stands for ‘Not Available’, as in, the data is missing or your formula is broken (probably the latter, lets be honest!). Time to run through some examples so we can better understand this problem.
So lets say we want to VLOOKUP the net worth of Beff Jezos, he sell’s books or something right?
Leading or Trailing Spaces
We’ve specified an EXACT match using the 0 (false) argument, however, we have some trailing spaces on the end of the lookup criteria.
Inaccurate Table Range
Oops! It looks like our table_range (B4:C6) isn’t selecting the correct cells. This usually happens when we enter a formula and drag down the cells (using it’s RELATIVE reference).
To stop this from happening, it’s a good idea to use ABSOLUTE references. We’ll cover these in detail in a later article, but it just means (set in stone, like Excalibur – its not moving anywhere!). To switch to an ABSOLUTE reference just put $ signs on the table_range.
Searching Column Using Incorrect Lookup Value
Remember, a VLOOKUP function always searches the first column specified in the table_range.
Value Not Found
Sometimes the data you’re looking for just isn’t in the table. Maybe it doesn’t want to be found? Maybe it’s social-distancing? These are all important considerations.
This one is pretty simple to deal with. A #NAME error suggests, in your haste to make an amazing worksheet, you probably have either:
- A typo in your formula name (VLOOKUP)
- Used text where a number was expected
Both of these can be corrected fairly quickly by scanning over the entire formula. If you’re still struggling you can post a question on our Help Forum!
Ahh the #VALUE error – One of the more elusive error messages, but you may see it once in a while; like an Indian Chevrotain (google it, you wont be disappointed).
Col_Index_Value Less Than 1
If you’re a programmer, you may be used to indexes starting at 0. However, in the world of the worksheet the first column of a table_range is always 1. So we can’t ‘LOOKUP’ data in column numbers less than 1.
Lookup Value is over 255 Character Long
What kind of lunacy is this? Just shorten your lookup_value. Easy.
If you’ve read this far, you’ve either ignored all the text above or you’re really keen to know how we can remove this error messages from your worksheet(s).
Introducing the all-powerful (get-out-of-jail-free card) IFERROR!
The IFERROR formula can be used to encase any Excel formula. The first argument (value) is the place holder for said formula. The second argument (value_if_error) is shown on screen if the VLOOKUP returned any of the error messaged above.
The value_if_error argument can be
- Static number [eg 1729 – Interesting Number Paradox Anyone?]
- String of Text [eg “cofveve” – What did he mean?]
- Blank String [eg “”]
- Another Cell Reference [eg. K9 – Woof]
- Another Formula [eg IFERROR(First VLOOKUP, Second VLOOKUP)]
If you’re new to VLOOKUP, and want to learn how to use it – first check out the the article below!