Gnumeric #REF!: Check Range Bounds and Address Style

Scope: Gnumeric desktop, English 1.12-series manual checked October 21, 2026. Distribution builds and translations may differ. These new exercises have not been executed in Gnumeric; results below are documentation-supported expectations and independently checked arithmetic or logic, not native test observations. Work in a disposable workbook, not your original.

The requested location must exist

A reference error is not the same as an empty result. Gnumeric’s Lookup reference documents different routes to #REF!: INDEX can ask outside its supplied array; INDIRECT can receive text invalid for its selected reference style; VLOOKUP can request a column outside its table.

For diagnosis, record the actual supplied range and the requested position. Count within that range, not from the worksheet’s first row or column. Do not delete source rows to reproduce an error in a real workbook.

Reproduce an out-of-range request

In a disposable sheet enter 11 in B2 and 17 in B3. In E2 enter =INDEX(B2:B3,3,1). The range has two rows and one column; requesting its third row is expected to produce #REF!.

If the intent is the second item, repair E2 to =INDEX(B2:B3,2,1). The expected result is 17. Change B3 to 23 and verify E2 becomes 23. Set the row argument to 1 and verify 11. Restore B3 to 17 and the argument to 2.

Alternatively, if a third record really exists, the table boundary may be wrong. Confirm and extend the range to include that record. Do not append an arbitrary value just to eliminate the error. These are two different repairs: correcting the requested position versus correcting the table definition.

Distinguish address text from an index

In E4 enter =INDIRECT("B0",TRUE). Row zero is not a valid A1 address, so the documented invalid-reference condition applies. Change it to =INDIRECT("B3",TRUE); the expected result is 17.

The optional argument chooses the interpretation of the text. For a numbered-style control, =INDIRECT("R3C2",FALSE) should also return 17. Compare the complete address and style flag together. Do not replace TRUE with FALSE arbitrarily: that changes the address language, not the source data. The INDIRECT guide contains more detailed syntax and separately scoped earlier native evidence; these new fixtures were not executed.

Trace before suppressing

In an imported workbook, compare the formula with the original application or a preserved source copy when available. A numeric value returned after your change is not sufficient proof: it could be the wrong cell’s value. Check the source label, units and expected response to one controlled input change.

For VLOOKUP, compare the requested column with the table width; for INDEX, compare both row and column with the supplied range. For INDIRECT, inspect the generated string before it is interpreted. Keep those intermediate checks visible until the repair is accepted.

Do not use IFERROR to turn every invalid reference into zero. That could make missing source records look like real zero amounts. See INDEX, VLOOKUP and general troubleshooting for the next investigation.