Excel DGET Function — Extract One Record, and Fail Loudly When There Isn't One

TL;DR — DGET(database, field, criteria) returns the single value from a column where your criteria range matches exactly one row. Its signature move is that it errors on purpose: #NUM! when more than one row matches, #VALUE! when none does. Everyone meets those as bugs; they're the feature. Where VLOOKUP silently returns the first of several duplicates, DGET refuses and tells you your "unique" key isn't unique. And because the conditions live in a criteria table, DGET does multi-column lookups (Region and Product) with no helper column. Reach for it when there should be exactly one match and you want Excel to prove it. Works in every Excel version.

=DGET(A1:E200, "Amount", H1:H2)      ' the Amount of the one row matching H1:H2
=DGET(A1:E200, "Rep", H1:I2)         ' two conditions (H1:I2) = an AND lookup, no helper column
=IFERROR(DGET(A1:E200, "Amount", H1:H2), "not unique / not found")   ' production-safe

VLOOKUP and XLOOKUP are built to be forgiving: give them a key, they hand back the first thing that matches and never look further. That's usually what you want — and occasionally a disaster, because a duplicate key returns a plausible wrong answer with no warning. DGET is the opposite temperament. It assumes the match is unique and stops with an error the moment that's false. Once you see that as its purpose rather than its flaw, it becomes the sharpest data-validation lookup in Excel.

What you'll learn

The mental model: a lookup that asserts uniqueness instead of guessing
The three arguments, and how DGET reuses the criteria-range idea
#NUM! vs #VALUE! — the two errors that are actually telling you something
Multi-condition lookups (Region and Product) with no concatenation trick
When DGET beats XLOOKUP — and the cases where it's the wrong tool
Wrapping it safely without hiding what the errors mean

The mental model: the lookup that refuses to guess

Think of DGET as a lookup with a contract: "There is exactly one row that matches. Give me a field from it." If the contract holds, you get the value. If it doesn't — zero matches, or two — DGET won't paper over it. That single behavioral choice is what separates it from every other lookup.

' criteria block H1:H2 — one condition:
'   H1: OrderID
'   H2: 10248
=DGET(A1:E200, "Amount", H1:H2)      ' -> the Amount for order 10248, IF that ID is unique

VLOOKUP would happily return the first order 10248 even if the ID appears three times. DGET returns #NUM! instead — which, when OrderID is supposed to be a primary key, is exactly the alarm you want. It turns "look this up" into "look this up and confirm my key is really unique," for free.

The three arguments (same shape as the whole family)

=DGET(database, field, criteria) — identical to DSUM and DCOUNT:

database — the table including its header row (A1:E200). The headers are how the field and criteria are matched to columns; omit them and DGET breaks.
field — the column to return a value from, named by quoted header ("Amount"), a cell holding the header, or a column number. The quoted header is the readable, edit-proof choice.
criteria — the range holding your header-and-conditions block, exactly like the other D-functions. Same rules: same row = AND, stacked rows = OR, headers must match the data character for character.

Everything you know about building a criteria range carries straight over. The only thing that changes is the promise: DGET expects that block to select one row.

#NUM! and #VALUE! are the whole point

Most functions have one error state. DGET has two, and they mean opposite things — learning to read them is 90% of using it well.

=DGET(A1:E200, "Amount", H1:H2)
'   -> the value        : exactly one row matched (the happy path)
'   -> #NUM!            : MORE THAN ONE row matched — your key isn't unique
'   -> #VALUE!          : NO row matched — nothing fits the criteria

#NUM! means "too many." Two or more rows satisfy your criteria. If the field was meant to be a unique key, you just found a duplicate you didn't know about — a genuinely useful discovery. If you expected several matches, DGET is simply the wrong function (you want a sum, a filter, or a first-match lookup).
#VALUE! means "none." Nothing matched — a mistyped key, a criteria header that doesn't match the data, or a value that isn't there.

Because the two errors are diagnostic, resist the urge to swallow both with a blanket IFERROR(""). "Duplicate key" and "not found" are different problems and often deserve different handling.

Multi-condition lookups, no helper column

Here's where DGET quietly out-designs VLOOKUP. Because the conditions live in a criteria table, adding a second condition is just adding a column — you get an AND-lookup with no concatenated helper key.

' Find the Rep for the West + Widgets row:
'   H1: Region    I1: Product
'   H2: West      I2: Widgets
=DGET(A1:E200, "Rep", H1:I2)         ' matches on BOTH columns at once

The classic VLOOKUP workaround for a two-column key is to build a helper column of Region&Product and look up "West"&"Widgets" — fragile and cluttered. DGET needs none of it: put two headers on the top row, two conditions beneath, and the match is AND across the row. And you still get the uniqueness guarantee — if West + Widgets isn't unique, you'll know.

When DGET beats XLOOKUP — and when it doesn't

DGET is a specialist, and using it outside its lane causes most of the frustration people report.

Reach for DGET when:

The match should be unique and you want that enforced — reconciling on an invoice number, pulling one config value, validating a key.
You need a multi-column AND lookup and would rather not build a helper key.
The criteria should be visible and editable on the sheet.

Use XLOOKUP or INDEX/MATCH instead when:

Duplicates are expected and you want the first (or last) match, not an error. DGET will only ever throw #NUM! here.
You're copying the lookup down hundreds of rows — XLOOKUP is leaner than a criteria block per row.
You need approximate/nearest matching. DGET is exact-match only.

The one-line test: is more than one match an error, or an expectation? If it's an error, DGET is the only lookup that treats it as one.

Live formulas shouldn't show raw #NUM!/#VALUE! to end users, but a blanket catch throws away the diagnosis. Distinguish the two when it matters:

' Simple, when either problem means the same to the user:
=IFERROR(DGET(A1:E200, "Amount", H1:H2), "No single match")

' Better, when duplicate vs missing need different handling:
=IF(DCOUNT(A1:E200, "Amount", H1:H2) = 1,
    DGET(A1:E200, "Amount", H1:H2),
    IF(DCOUNT(A1:E200, "Amount", H1:H2) = 0, "Not found", "Duplicate key!"))

The second pattern uses DCOUNT on the same criteria to report which failure occurred — turning DGET's strictness into a clear message instead of an error code. That's DGET at its best: not a fussy lookup, but a lookup that makes bad data impossible to ignore.

How ExcelMaster helps

DGET rewards precise criteria blocks and punishes sloppy ones, which is exactly the fiddly part. Tell ExcelMaster "get the amount for the one West + Widgets order" and it builds the two-column criteria table, matches the headers to your data, and writes the =DGET(...). Hand it a DGET throwing #NUM! and it explains which rows collide — surfacing the duplicate key you didn't know you had, rather than just hiding the error.

Frequently asked questions

Why does DGET return #NUM!?

Because more than one row matches your criteria. DGET returns exactly one value and treats multiple matches as an error — often a helpful one, since it means a key you assumed was unique isn't. If you genuinely expect several matches, use SUMIFS, FILTER, or a first-match lookup like XLOOKUP instead.

Why does DGET return #VALUE!?

Because no row matches. The usual causes are a mistyped criterion, a criteria header that doesn't match a database header exactly, or a value that simply isn't in the table. Check the header spelling first — it's the most common culprit across all database functions.

How is DGET different from VLOOKUP?

VLOOKUP returns the first match and ignores any duplicates silently. DGET insists on a single match and errors (#NUM!) if there's more than one. DGET also matches on multiple columns through its criteria range, where VLOOKUP needs a concatenated helper key. Use VLOOKUP/XLOOKUP for forgiving first-match lookups; use DGET when uniqueness must be guaranteed.

Can DGET match on more than one column?

Yes — that's a core strength. Put each header on the top row of the criteria range and each condition directly beneath, on the same row for AND logic: Region=West and Product=Widgets across one row matches only rows satisfying both, with no helper column.

Does DGET work in Excel 2016 and older?

Yes. DGET, like the rest of the database functions, has been in Excel for decades and behaves identically in Excel 2016, 2019, 2021, and 365. There's no version-availability catch to worry about.

Tested in

Tested in: Excel 365 (Windows 11) — last verified 2026-07-20.