VLOOKUP and HLOOKUP raise a different error for a bad index in LibreOffice
Ask VLOOKUP for column 5 of a two-column table and Microsoft documents the answer as
#REF! — you asked for a reference that does not exist. LibreOffice Calc reports the
same broken formula as #VALUE!. The lookup fails either way, so this is not a wrong
number; it is a wrong error, and error identity is what conditional logic branches on.
The surprise
=VLOOKUP("a",A1:B3,5,FALSE) is #REF! per Microsoft and
#VALUE! in LibreOffice; =HLOOKUP("a",A1:C2,5,FALSE) does the same. Every
other case we ran — exact match, approximate match, wildcards, and the ordinary not-found
#N/A — agrees between the two engines. Only the out-of-range index forks, and it
forks in exactly the place where hand-written error handling tends to be specific.
Executed results
The VLOOKUP rows use A1:B3 = a/1, b/2, c/3; the wildcard row uses
A1:B3 = apple/1, banana/2, avocado/3. The HLOOKUP rows use
A1:C1 = a, b, c over A2:C2 = 1, 2, 3.
| Formula | Excel (documented) | Google Sheets | LibreOffice Calc 25.8.7.3 (executed) |
|---|---|---|---|
| =VLOOKUP("a",A1:B3,5,FALSE) | #REF! | Not yet executed | #VALUE! |
| =HLOOKUP("a",A1:C2,5,FALSE) | #REF! | Not yet executed | #VALUE! |
| =VLOOKUP("z",A1:B3,2,FALSE) | #N/A | Not yet executed | #N/A |
| =HLOOKUP("z",A1:C2,2,FALSE) | #N/A | Not yet executed | #N/A |
| =VLOOKUP("b",A1:B3,2,FALSE) | 2 | Not yet executed | 2 |
| =VLOOKUP(3,A1:B5,2,TRUE) | 20 | Not yet executed | 20 |
| =VLOOKUP("a*",A1:B3,2,FALSE) | 1 | Not yet executed | 1 |
| =HLOOKUP(6,A1:E2,2,TRUE) | 50 | Not yet executed | 50 |
The Excel column is the documented-expected value recorded in our test corpus (for the two
out-of-range rows our corpus records #REF! as Microsoft's documented result for an
index beyond the table, citing the VLOOKUP documentation); we did not run Excel. The LibreOffice column is
what our harness computed by recalculating the workbook in LibreOffice Calc 25.8.7.3. The good news
is how much agrees: only VLOOKUP_col_index_out_of_range and
HLOOKUP_row_index_out_of_range diverge.
Consistent across LibreOffice versions
We ran both formulas through 24.2.0.3, 24.8.7.2, 25.2.0.3 and 25.8.7.3. All four builds returned
#VALUE!. This is not a regression in a recent release — it is how LibreOffice has
been classifying the error throughout the versions we test.
Why it happens
The two engines disagree about what kind of mistake an oversized index is. Excel treats it as a
reference problem: you named a column that is not in table_array, so the result is
#REF!. LibreOffice treats it as a bad argument — the third parameter is
outside its permitted range — and reports #VALUE!, the same code it returns for
other out-of-domain arguments in our corpus such as =SQRT(-16) and =LN(0)
(see our guide on
#NUM! vs #VALUE! domain errors).
LibreOffice's #VALUE! is a broad bucket; Excel's error codes are narrower.
How to migrate safely
Stop branching on the specific error. =IFERROR(VLOOKUP(...),"not found") and
=ISERROR(...) catch both codes and behave identically in both engines — every
IFERROR and ISERROR case in our corpus matched in LibreOffice 25.8.7.3.
Anything narrower will not survive the move: an =ISNA(...) guard catches neither error,
and an ERROR.TYPE(...)=4 test never matches in LibreOffice (see
ERROR.TYPE codes across engines).
Better still, remove the fragile index. =INDEX(B1:B3,MATCH("a",A1:A3,0)) names the
result column directly instead of counting to it, and every INDEX and
MATCH case in our corpus matched in LibreOffice 25.8.7.3. If you can require a recent
build, XLOOKUP is the cleanest fix, because its if_not_found argument
removes the error entirely: =XLOOKUP("a",A1:A3,B1:B3,"not found") returned
"not found" in our executed run. Note the version floor, though —
XLOOKUP returned #NAME? in 24.2.0.3 and only started evaluating in
24.8.7.2. If a formula must keep its numeric index, guard it:
=IF(colidx>COLUMNS(table),NA(),VLOOKUP(key,table,colidx,FALSE)) makes the mistake
mean the same thing in both engines.
Honest limits
The Excel column is Microsoft's documented behaviour as recorded in our test corpus, not values we executed in Excel. Google Sheets is not yet run through our harness, so that column is left honest. The LibreOffice column is executed output from LibreOffice Calc 25.8.7.3, cross-checked against our 24.2.0.3, 24.8.7.2 and 25.2.0.3 runs.