Contact
12 programs
Explore the site

Google Sheets #REF!: Separate Range Boundaries from Import Permission

Treat #REF! as a starting point, not a single repair instruction. A local formula can request a nonexistent position; an IMPORTRANGE formula can instead need an explicitly authorized connection. Identify which kind of formula failed before changing its range or permissions.

Local range: count from the selected range

In a scratch sheet, enter Desk in A2, 4 in B2, Chair in A3 and 9 in B3. Place this formula in D2:

=VLOOKUP("Chair",A2:B3,3,FALSE)

A2:B3 has two columns, not three. The documented expected error is #REF!. If the intended result is the count in B, use =VLOOKUP("Chair",A2:B3,2,FALSE) and expect 9. Google’s VLOOKUP troubleshooting says to count columns within the selected range, starting at 1.

Repair the intended field, not just the number. Expanding A2:B3 to A2:C3 without knowing what C contains could return unrelated data.

Calculated address: stay inside the sheet

A different reference failure can arise before any lookup occurs. In an otherwise empty scratch sheet, put 27 in A1. Enter =OFFSET(A1,-1,0) in D1. The target would be above row 1, so the expected result is #REF!. =OFFSET(A1,0,0) should return 27. Google’s OFFSET reference documents references beyond the upper or left boundary.

For a real formula with a calculated offset, inspect the offset value separately. Clamping a negative value to zero is not automatically correct: it may silently substitute the first record for a missing earlier record.

Imported data: review access, not arithmetic

Google’s IMPORTRANGE documentation describes a #REF! prompt when two spreadsheets need connecting. Confirm the exact source document, destination document and intended audience before granting access. After connection, destination editors can use IMPORTRANGE to retrieve other parts of that source, not just the initial range.

If you lack access, request it from the owner and wait. Do not make the source public, bypass restrictions or copy private data into a broader audience to eliminate the error. This reference does not include a live connection test or instruct an assistant to grant permission automatically.

Preserve the diagnostic signal

IFERROR cannot prove that a reference points to the right record or that an import is authorized. Keep the failing formula available while resolving the cause. After a repair, compare a known source value and record count rather than accepting any non-error output.

Checked October 21, 2026 for the rolling Google Sheets web service. Expected values and coordinates were independently checked; there was no signed-in application execution, permission change or native screenshot. Examples use English function names and comma separators; adapt to the spreadsheet’s locale. No Excel-specific #SPILL! label is assumed for Sheets.

Continue with OFFSET, IMPORTRANGE or Sheets troubleshooting.