ExcelFunctions.net

Search site:

The #REF! error is generally produced when you attempt to access locations that do not exist.

When using the Vlookup function, the Vlookup #REF! error may be due to either:

- the supplied col_index_num argument not being recognised as a valid numeric value (should be an integer between 1 and the number of columns in the supplied table-array).

or

- the function referring to cells that do not exist.

The following steps consider both of these problems.

Step 1 - Check the col_index_num argument

First carry out a simple check on the col_index_num argument. This should be a positive integer that is less than or equal to the number of columns in the table_array.

Step 2 - Check for #REF! values in the Formula

If the Vlookup #REF! error is caused by an attempt to reference cells that do not exist, this will show up as one or more #REF! errors in the formula. Therefore, if you look at your Vlookup formula, you will clearly see this.

There are two common reasons why your function may attempt to reference cells that don't exist. The simplest of these is when one (or more) of the function arguments are input as references to cells, which are later deleted.

Also, this problem frequently arises due to relative references in copied formulas.
For example, if you use the mouse to select an entire worksheet (named Sheet1) as the table_array for a
Vlookup function, this will be inserted into the Vlookup function as the range **Sheet1!1:1048576**
(in Excel 2007 and later versions of Excel).

If this Vlookup function is then copied down a row, the
relative referencing
within the formula, changes the table_array in the copied formula, to **Sheet1!2:1048577**.
However, the row 1048577 doesn't exist, as so this results in the #REF! error being inserted into the copied
Vlookup formula instead.

Quick Fix to this Problem: Use Absolute References for the table_array

The simplest way to prevent a reference to a range from changing when copied to other cells is to use absolute references within the formula.

E.g. the reference **Sheet1!1:1048576** should be changed to **Sheet1!$1:$1048576**.

Note that the $ signs keep this reference constant when the formula is copied to other cells and will therefore avoid the Vlookup #REF! error.

See the Excel Cell References page for more information on cell references.

BETTER Fix to this Problem: Be More Specific When Defining the table_array

Your Vlookup function will be more efficient if the table_array only includes the cells that contain the table_array data, rather than using entire columns, or an entire worksheet.

However, you will still need to use absolute references if you want the table_array to remain constant when the formula is copied to other cells.

Further Vlookup troubleshooting tips are provided, in the form of a handy Vlookup Quick Reference Card, on the Microsoft Office website.