=VLOOKUP(A2, Table, 3, FALSE) -- ensure table has at least 3 columns The #REF! error means 'Invalid Reference'—a cell, row, column, or worksheet referenced in the formula was deleted, moved, or falls outside the table boundaries.
How to Fix the #REF! Error in Excel & Google Sheets
The #REF! error stands for “Invalid Cell Reference”. Unlike other errors that occur due to data values, #REF! occurs when the underlying cell address referenced by the formula no longer exists.
🔍 The 3 Main Causes of #REF!
1. Deleted Rows or Columns
If a formula had =A1 + B1 + C1 and you right-click Column B and select Delete Column, the formula becomes =A1 + #REF! + C1.
- Fix: If immediate, press
Ctrl + Zto undo. Otherwise, edit the formula and remove the#REF!term.
2. VLOOKUP Column Index Exceeds Table Width
=VLOOKUP(A2, B2:D10, 5, FALSE)- Range
B2:D10contains only 3 columns (B, C, D). - Asking for column
5causes an immediate#REF!error. - Fix: Change
5to a valid column number (1, 2, or 3).
3. Deleted or Renamed Worksheet Tabs
If a formula references ='Quarterly Data'!A1 and that worksheet tab is deleted:
- Fix: Point the formula to an existing sheet:
=NewSheet!A1.
Frequently Asked Questions
How do I fix a formula that became =SUM(#REF!) after deleting a row?
If you just deleted the row, immediately press Ctrl+Z to undo. If the file was saved, manually re-enter the valid range reference =SUM(B2:B20).
Why does VLOOKUP return #REF! instead of #N/A?
If your table range is A2:C10 (3 columns total) and you request column 4 in VLOOKUP, it throws #REF! because column 4 does not exist in that range.