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 following steps consider both of these problems.
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.
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.
Further Vlookup troubleshooting tips are provided, in the form of a handy Vlookup Quick Reference Card, on the Microsoft Office website.