One reason why the Vlookup #REF! error may be returned, is if the supplied col_index_num argument is < 1 or is not recognised as a numeric value.
However, more commonly, the #REF! error is caused by attempts to reference cells that don't exist.
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 or 2010).
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.