#REF! Error in Google Sheets
What it means: A reference in the formula is broken — the cell, row, column or sheet it pointed to no longer exists.
Quick fix
=IFERROR(VLOOKUP(A2, Sheet2!A:B, 2, FALSE), "")
Wrap the lookup in IFERROR so a broken reference returns a blank instead of #REF! while you track down the real cause.
Why you see #REF! — and how to fix each cause
1. You deleted a row, column, or sheet the formula used
If a formula referenced B5 and you deleted row 5, the reference has nothing to point at, so it collapses to #REF!.
Fix: Press Ctrl+Z to undo the deletion, then re-point the formula before deleting again. Editing the formula to a valid cell also clears it.
2. A VLOOKUP or INDEX column index is out of range
Asking VLOOKUP for column 3 of a two-column range returns #REF! because there is no third column.
Fix: Lower the column index to fit the range, or widen the range.
=VLOOKUP(A2, Sheet2!A:C, 3, FALSE)
3. IMPORTRANGE lost access or the source range moved
If the source spreadsheet's tab was renamed or access was revoked, IMPORTRANGE returns #REF! (hover it and click Allow access if prompted).
Fix: Re-grant access, or fix the sheet/range name inside the IMPORTRANGE string.
Before and after
| Broken | Working |
|---|---|
=A1+B1 (row containing B1 was deleted) |
=A1+B2 |
After deleting a row, references shift; re-point the formula to the cell that now holds your value.
How to stop it happening again
Before deleting rows or columns, click a cell and use Data → Named ranges or check for dependent formulas. Using named ranges or whole-column references like A:A instead of fixed cells makes formulas survive row deletions.
FAQ
Does #REF! fix itself?
No. It stays until you edit the formula to point at a valid reference or undo the deletion that broke it.
Can I find every #REF! at once?
Use Ctrl+H (find and replace), tick "Search within formulas", and search for #REF! to locate all affected cells.
→ Google Sheets formulas cheat sheet — 34 copy-paste formulas