DGET Function
Returns one value from the only record that matches your criteria.
Category
Database
Available in
All versions, Microsoft 365
Returns
The single matching value
Want to practice DGET in a real spreadsheet? Open a blank sheet in our Practice Sandbox and try it with live data, with the AI Excel tutor beside you. No Excel needed — just a free account.Try it free in the Sandbox
Overview
DGET is a lookup with conditions: it finds the one record that meets the criteria and returns the value from the field you choose.
It's strict on purpose — no match gives #VALUE!, and more than one match gives #NUM! — which makes it a handy duplicate check.
Syntax
=DGET(database, field, criteria) Explain
Arguments
| Argument | Required? | What it does |
|---|---|---|
| database | Yes | The whole list, including the header row — e.g. A1:E7. Each row is a record, each column a field. |
| field | Yes | Which column to calculate: its header in quotes ("Sales"), a cell holding the header, or its position (5 = the fifth column). |
| criteria | Yes | A small range with at least one header copied exactly from the list and the conditions underneath, e.g. G1:G2. |
Examples
Example 1 — Who sold the Pens?
Exactly one order is for Pens.
G5fx=DGET(A1:E7,"Rep",G1:G2)
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Region | Rep | Product | Units | Sales | Product | |
| 2 | North | Amara | Paper | 40 | 520 | Pens | |
| 3 | South | Ben | Toner | 12 | 576 | ||
| 4 | North | Chloe | Toner | 8 | 384 | Rep | |
| 5 | East | Dev | Paper | 25 | 325 | Amara | |
| 6 | North | Amara | Pens | 100 | 120 | ||
| 7 | South | Ella | Paper | 30 | 390 |
=DGET(A1:E7,"Rep",G1:G2) Explain
Example 2 — Narrow down until one record is left
Toner alone matches two orders (#NUM!). Adding Region = South leaves just one.
G5fx=DGET(A1:E7,"Sales",G1:H2)
| A | B | C | D | E | F | G | H | |
|---|---|---|---|---|---|---|---|---|
| 1 | Region | Rep | Product | Units | Sales | Product | Region | |
| 2 | North | Amara | Paper | 40 | 520 | Toner | South | |
| 3 | South | Ben | Toner | 12 | 576 | |||
| 4 | North | Chloe | Toner | 8 | 384 | Sales | ||
| 5 | East | Dev | Paper | 25 | 325 | 576 | ||
| 6 | North | Amara | Pens | 100 | 120 | |||
| 7 | South | Ella | Paper | 30 | 390 |
=DGET(A1:E7,"Sales",G1:H2) Explain
Common mistakes
- #NUM! More than one record matched. Add another condition to narrow it down.
- #VALUE! No record matched, or the field name is wrong.
- Partial text matches. "Pen" would also match "Pens" and "Pencils". Use ="=Pens" for an exact match.
Notes
- Criteria rules: conditions on the same row must all be true (AND); conditions on separate rows are alternatives (OR). Text such as Nor matches anything that begins with it — type ="=North" for an exact match. Use >500, <>East, or wildcards like *Paper*.
- Modern alternatives: XLOOKUP with several conditions, or FILTER.
- Related functions: DSUM, DAVERAGE, DCOUNT, DCOUNTA, DGET, DMAX, DMIN, DPRODUCT, DSTDEV, DSTDEVP, DVAR, DVARP.

Author
Kyle Stott
Certified Microsoft Excel Expert
Kyle has worked professionally with Microsoft Excel for over a decade and has consulted on and taught Excel best practices to over 400 companies across 30+ countries.