ExcelFunctions.net

Search Site:

The #REF! error is generally produced when you attempt to use a reference that does not exist.

When using the Vlookup function, the Vlookup #REF! error occurs if either:

- the supplied col_index_num is greater than the number of columns in the supplied table_array

or

- the function is attempting to reference 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 value of your 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 be able to see this.

B | |
---|---|

2 | =VLOOKUP( A2, Sheet1!#REF!, 2, FALSE ) |

There are two common reasons why this may have occurred.

One reason is that the function previously referred to valid cells, but one (or more) of these have now been 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 current versions of Excel).

If this Vlookup function is then copied down a row, the range **Sheet1!1:1048576** will
automatically update to **Sheet1!2:1048577**. However, the row 1048577 does not exist,
so this results in the #REF! error being inserted into formula instead.

B | |
---|---|

1 | =VLOOKUP( A1, Sheet1!1:1048576, 2, FALSE ) |

2 | =VLOOKUP( A2, Sheet1!#REF!, 2, FALSE ) |

Quick Fix to this Problem: Use Absolute References

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.