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

ArgumentRequired?What it does
databaseYesThe whole list, including the header row — e.g. A1:E7. Each row is a record, each column a field.
fieldYesWhich column to calculate: its header in quotes ("Sales"), a cell holding the header, or its position (5 = the fifth column).
criteriaYesA 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)
ABCDEFG
1RegionRepProductUnitsSalesProduct
2NorthAmaraPaper40520Pens
3SouthBenToner12576
4NorthChloeToner8384Rep
5EastDevPaper25325Amara
6NorthAmaraPens100120
7SouthEllaPaper30390
=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)
ABCDEFGH
1RegionRepProductUnitsSalesProductRegion
2NorthAmaraPaper40520TonerSouth
3SouthBenToner12576
4NorthChloeToner8384Sales
5EastDevPaper25325576
6NorthAmaraPens100120
7SouthEllaPaper30390
=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.
Kyle Stott

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.