DGET

What it does

DGET(database, field, criteria)

DGET(database, field, criteria) selects the records of database that satisfy criteria and returns the value held in the column named by field — but only if exactly one record matches.

|R| = 0  →  #VALUE!
|R| = 1  →  the value of cell (the matching record, field column)
|R| > 1  →  #NUM!

The uniqueness requirement is the point of the function. DGET is a lookup that refuses to guess: where VLOOKUP silently returns the first of several matches, DGET reports that the question was ambiguous.

The selection stage — header matching, AND across a criteria row, OR down the rows, blank criteria cells, and criteria-expression parsing — is identical for all twelve database functions and is explained once in the criteria-range mechanism.

Arguments

Three arguments, all required at the call site; the reference implementation declares an arity of exactly 3.

• database — the header-plus-records range or array.
• field — the column selector, as header text or as a 1-based column index within the database range. Required: unlike DCOUNT and DCOUNTA, DGET has nothing to return without a column.
• criteria — the header-plus-conditions range or array. Note the family rule that bites hardest here: a criteria range with no condition rows, or with an all-blank condition row, selects every record — so on any database with two or more records that mistake produces #NUM!, not the "first record" a reader might expect.

Try it

Quirks

Result and edge cases

Returns whatever kind the selected cell holds: Number, Text, or Logical.

• The selected cell holds an error value: that error is returned. DGET passes the cell through rather than diagnosing it.
• Text is returned as text. DGET is the only family member that can return a non-numeric result, which matters when its output feeds arithmetic downstream.
• Arrays: inline array literals resolve to grids and are accepted; DGET is not a lift kernel, and it never returns an array.

Errors

How to implement

Notes for implementers

1. Count matches before reading any cell. The uniqueness test governs the whole result, and an implementation that reads the first match eagerly will produce a value where an error is required.
2. The two error codes are not interchangeable and are not #N/A. Getting the pair backwards is a silent semantic bug: both are errors, so a naive test that only checks ISERROR will not catch it.
3. Numeric criteria comparison is not exact double comparison — see the note in the shared mechanism. For DGET the consequence is sharper than for the aggregates: a tolerant comparison can turn a unique match into a double match, converting a value into #NUM!.

Rust code

4 dget "DGET" (3, 3) [R "database", V "field", R "criteria"] |s, a, sv| { database(s, a, sv, Kind::Get) }

No implementation has been published with a Handbook test suite for this function.

Version differences

No comparison between specific Excel builds is recorded here yet. This does not establish that all builds behave alike.

Alternatives

Relationships

• Siblings: the other eleven database functions, which share the selection stage and reduce the field column instead of selecting from it. Where they answer "how much", DGET answers "which".
• Nearest modern equivalents: XLOOKUP and FILTER. FILTER is the closest in spirit — criteria in, records out — but returns every match as a spilled array rather than insisting on uniqueness. XLOOKUP returns the first match and takes a not-found argument. Neither supersedes DGET; Excel retains all three.
• Confused with: VLOOKUP and INDEX/MATCH, both of which return the first match without complaint. Replacing DGET with VLOOKUP to "fix" a #NUM! discards the very check that was reporting a real ambiguity in the data.

Named variants

No named variants are recorded yet.