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:
or
The following steps consider both of these problems.
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.
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 ) |
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.
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.