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/AYou’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)
#VALUEYour col_index_num is liklely less than 1. We can only VLOOKUP left to right, starting with the first column (column 1)
Table 1: Excel VLOOKUP Error Messages

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.

#NA Error

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.

VLOOKUP Data Table
Table 2: Being a billionaire must be nice!

So lets say we want to VLOOKUP the net worth of Beff Jezos, he sell’s books or something right?

Leading or Trailing Spaces

=VLOOKUP (“Beff Jezos “, B:C, 2, 0)
=VLOOKUP (“Beff Jezos”, B:C, 2, 0)

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

=VLOOKUP (“Beff Jezos”, B4:C6, 2, 0)
=VLOOKUP (“Beff Jezos”, $B$1:$C$6, 2, 0)

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

=VLOOKUP (“Beff Jezos”, A1:C6, 3, 0)
=VLOOKUP (“Rainforest”, $A$1:$C$6, 2, 0)

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.

#NAME

=VLOKUP (“Beff Jezos”, B1:C6, 2, 0)
=VLOOKUP (“Beff Jezos”, B1:C6, TWO, 0)

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!

#VALUE

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

=VLOOKUP (“Beff Jezos”, B1:C6, 0, FALSE)

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.

Error Handling

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!

=IFERROR (value, value_if_error)

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

Pre-requisites

If you’re new to VLOOKUP, and want to learn how to use it – first check out the the article below!