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.

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.

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 desktop 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

Excel and LibreOffice 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 every engine here — 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 everywhere.

Honest limits

The Excel column is Microsoft's documented behaviour as recorded in our test corpus, not values we executed in Excel. The Google Sheets column is executed output from a Drive import on 2026-08-29: Sheets returns #REF! for an out-of-range index, matching Excel’s documentation, so LibreOffice is the only engine that answers #VALUE!. Excel for the web — a separate application from the desktop product, and the third engine we execute — returned #REF! for both =VLOOKUP("a",A1:B3,5,FALSE) and =HLOOKUP("a",A1:C2,5,FALSE) when we recalculated the corpus on OneDrive on 2026-09-01, so the documented code is measured in a Microsoft engine as well. 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.

Check before you migrate

A note on which Excel this is. The Excel column in the tables above is Microsoft’s documented behaviour for desktop Excel, as recorded in our test corpus — we do not run desktop Excel, and no value in that column is a measurement. Excel for the web is a different application with its own calculation engine, and that one we do run (recalculated on OneDrive, 2026-09-01). Its measured results are published on each function’s own page rather than in these guide tables. Because we have no desktop run to compare against, a disagreement between an Excel-web measurement and the documented column is genuinely ambiguous: it may mean the web engine diverges from the desktop one, or that the documentation is wrong about both. We do not claim to know which.