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 (documented) | Google Sheets (documented) | 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 | Not yet executed | 200 |
| =MODE.SNGL(1,2,2,3,4) | 2 | Not yet executed | 2 |
| =MODE.SNGL(1,1,2,2) | 1 | Not yet executed | 1 |
| =MODE(A1:A4) — no repeated value | #N/A | Not yet executed | #VALUE! |
The Google Sheets entries in the first two rows are the only ones on this site's guides that are
filled in, and they come from the cross-app note recorded against those two cases in our test corpus
— documented behaviour, not something we executed. Everywhere else the Sheets column is left
honest. The Excel column is documented behaviour too. 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.
Version coverage
Be precise about what we have run here. DGET and MODE.SNGL entered our
test corpus with the LibreOffice Calc 25.8.7.3 run, which covers 277 functions against 168 in the
24.2.0.3 and 24.8.7.2 runs, so we have executed results for those two functions in 25.8.7.3 only
— their absence from the earlier files means the cases were not run, not that the functions
were missing. The MODE(A1:A4) row is the exception: it has been in the corpus
throughout and returned #VALUE! in 24.2.0.3, 24.8.7.2, 25.2.0.3 and 25.8.7.3 alike,
which is good reason to think the MODE.SNGL result is long-standing rather than new.
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 both engines — 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 Excel or Google Sheets. The Excel values, and the two Google Sheets values, are
documented behaviour recorded in the notes attached to those cases in our test corpus. Note also how
these two cases are stored: because our corpus records the engine-observed error for them, the
expected value in the test file is LibreOffice's #VALUE!, with the Excel and Sheets
behaviour written into the case description — so these rows do not appear in our automated
mismatch counts even though they are genuine cross-app divergences. The LibreOffice values are
executed output from LibreOffice Calc 25.8.7.3.