Reasons for VLOOKUP #ref error
#REF errors are less likely in a VLOOKUP than #N/A but can still occur. There are two main reasons for this and it depends what the cell versus the formula look like.
#REF in the value but the formula looks fine
If the cell shows a #REF error (see cell C14 below) but the formula is OK it means one of two things.
The most common is that you are asking Excel to look for a column number (3 in the image below) that doesn’t exist in the table array. So in this formula we have told Excel that the table array is from $A$4 to $B$8 (blue lined box) which is only 2 columns, yet we are asking Excel to return the 3rd column.
Another possibility is that one of the cells that the VLOOKUP is looking at has a #REF error in it. In that case you are going to have to track down where the real error is and correct it.
#REF in the value and the formula
If you see a #REF error in the cell value (cell B14 below) and when you look in the formula you also see a #REF error, it means that a cell or cells that the VLOOKUP was looking at has been deleted.
By this we DON’T mean that the contents was deleted (when you click in a cell and click delete or backspace). This is where the entire sheet, row, column or cell have been removed from the spreadsheet.
Unfortunately unless you remember where it was looking or have an old version that doesn’t have the error, it is difficult to determine what it should look at.