DGET and MODE.SNGL return #VALUE! in LibreOffice where Excel and Sheets are documented to differ
Two functions in our corpus carry an unusual note: their failure case is documented the same way
for Excel and Google Sheets, and LibreOffice Calc disagrees with both.
DGET with more than one matching row is documented as #NUM! in Excel and in
Google Sheets; MODE.SNGL over a list with no repeated value is documented as
#N/A in both. Our executed LibreOffice run returns #VALUE! for each.
The surprise
Both of these errors are meaningful signals, not bugs. DGET returning
#NUM! is how you learn your criteria are not selective enough — the row you are
reading is ambiguous. MODE.SNGL returning #N/A is how you learn there is
no mode. In LibreOffice both arrive as #VALUE!, the generic bad-argument error, so a
formula that distinguished “ambiguous criteria” from “genuinely broken input”
can no longer tell them apart.
Executed results
The DGET rows use the database A1:B4 with headers
Region/Sales and rows East/100, West/200,
East/50, and a criteria range D1:D2 whose header is Region.
The criterion is West (one matching row) in the first row of the table and
East (two matching rows) in the second. The MODE(A1:A4) row uses four
distinct numbers.
| Formula | Excel, desktop (documented) | Google Sheets (executed 2026-08-29) | LibreOffice Calc 25.8.7.3 (executed) |
|---|---|---|---|
| =DGET(A1:B4,"Sales",D1:D2) — criterion East, two matches | #NUM! | #NUM! | #VALUE! |
| =MODE.SNGL(1,2,3) | #N/A | #N/A | #VALUE! |
| =DGET(A1:B4,"Sales",D1:D2) — criterion West, one match | 200 | 200 | 200 |
| =MODE.SNGL(1,2,2,3,4) | 2 | 2 | 2 |
| =MODE.SNGL(1,1,2,2) | 1 | 1 | 1 |
| =MODE(A1:A4) — no repeated value | #N/A | #N/A | #VALUE! |
The Google Sheets column is executed output from a Drive import on 2026-08-29, and it confirms what
was previously only documented: Sheets returns #NUM! for the two-match
DGET and #N/A for MODE.SNGL with no repeats, matching Excel on
every row of both tables. The Excel column is documented behaviour — we do not run desktop Excel. The LibreOffice column is what our harness
computed in LibreOffice Calc 25.8.7.3. Success cases agree in every engine we have data for: a
single-match DGET returns 200, and MODE.SNGL picks the right
value including the first-of-ties case.
Excel for the web is a separate application from the desktop product and it is the third engine we
execute. Recalculated on OneDrive on 2026-09-01 it returned #NUM! for the two-match
DGET and #N/A for both =MODE.SNGL(1,2,3) and
=MODE(A1:A4) — the documented codes, and the same ones Sheets returns. LibreOffice
remains the only engine here that collapses them to #VALUE!.
Version coverage
Be precise about what we have run here. DGET and MODE.SNGL entered our
test corpus part-way through, on the LibreOffice Calc 25.8.7.3 run, at a point when the earlier build
files did not yet carry them. All four build files now hold the same 586 functions, and both
DGET and MODE.SNGL return #VALUE! in 24.2.0.3, 24.8.7.2,
25.2.0.3 and 25.8.7.3 alike — so what was a single-build result when this page was written is now
a four-build one, and it is stable. The MODE(A1:A4) row has been in the corpus throughout
and behaves the same way.
Why it happens
It is the same pattern that runs through most of our error-code findings: LibreOffice funnels a
wide range of “this call cannot be answered” conditions into #VALUE!, where
Excel keeps narrower, more specific codes. #NUM! means “the arithmetic is out of
range”, #N/A means “there is no answer to give”, and LibreOffice
collapses both into “the argument is wrong”. Our
#NUM! vs #VALUE! guide
catalogues a dozen more instances of the same collapse.
How to migrate safely
Any =ISNA(MODE.SNGL(...)) check silently stops matching in LibreOffice, and so does
any test written against #NUM! from DGET. Use
=IFERROR(DGET(...),"check criteria") and =IFERROR(MODE.SNGL(...),"no mode"),
which behave identically in every engine in the table above — every IFERROR case
in our corpus matched in LibreOffice 25.8.7.3.
For DGET specifically, the better fix is to stop relying on the error at all. Count
the matches first: =IF(DCOUNT(db,field,crit)>1,"ambiguous",DGET(db,field,crit)) makes
the ambiguity explicit and portable, and both DCOUNT cases in our corpus matched in
LibreOffice 25.8.7.3. For MODE.SNGL, decide what a no-mode column should show and say so
with IFERROR rather than letting an error class carry the meaning.
Honest limits
We did not execute desktop Excel; its column is Microsoft’s documentation. Google Sheets and
LibreOffice were both executed. These two cases used to be stored with LibreOffice’s
#VALUE! as the expected value and the real Excel/Sheets behaviour written only into the
case description, which kept them out of our automated mismatch counts; that has been corrected, so
the expected values are now Excel’s documented #NUM! and #N/A and
both rows count as LibreOffice quirks like any other. The LibreOffice values are executed output
from LibreOffice Calc 25.8.7.3; the Sheets values are executed output from the 2026-08-29 Drive
import.
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.