DGET in Excel: Retrieve One Record and Detect Ambiguous Matches

=DGET(database, field, criteria)

The database range includes the header row. Field identifies the column to calculate: use a quoted heading such as “Units”, or its one-based position within the database. Here Units is field 3, even if the database is later moved to another part of the sheet. Criteria is a separate range containing matching field headers and at least one conditions row. All three arguments are required.

Enter this numeric database in A1:D7:

Put Team in F1 and Product in G1. In F2 enter ="=North"; in G2 enter ="=A". These formulas produce the exact-match criteria text =North and =A. The range F1:G2 selects the three North/A records, whose Units values are 2, 4 and 6 and Sales values are 100, 200 and 300.

Conditions in the same criteria row act together with AND. Alternative criteria rows act with OR. Keep the criteria and output cells outside the database, and leave room for the data to grow.

The initial North/A criteria select three records, so they are deliberately not unique. For a separate unique selection, put Team in J1 and Product in K1. Enter ="=South" in J2 and ="=A" in K2.

=DGET(A1:D7,"Sales",J1:K2)
=DGET(A1:D7,3,J1:K2)

Expected results are 500 and 10. Both come from the single South/A row; the field changes from Sales to Units.

=DGET(A1:D7,"Team",J1:K2)

Expected result: South. DGET retrieves the selected field’s value rather than calculating an aggregate, so its purpose is different from numeric-only statistics.

For more realistic records, a stable unique identifier is often safer than assuming a combination of descriptive labels is unique. The exact key should be chosen from the data model rather than invented by the function.

Using the initial North/A criteria, =DGET(A1:D7,"Sales",F1:G2) has documented #NUM! expectation because three records match. It does not return 100 merely because that is the first matching Sales value.

Change J2 to ="=East" while retaining Product A in K2. No record matches, so =DGET(A1:D7,"Sales",J1:K2) has documented #VALUE! expectation. These are different failure modes: ambiguous selection versus absent record.

A generic IFERROR wrapper would erase that distinction. Diagnose the matching count before replacing an error with an output label.

With the South/A criteria restored, =DCOUNTA(A1:D7,,J1:K2) has expected 1. With North/A it is three, and with East/A it is zero. Because field is omitted, this checks records rather than whether the retrieved field happens to be numeric.

An explicit review formula can make the intended gate visible:

=IF(DCOUNTA(A1:D7,,J1:K2)=1,DGET(A1:D7,"Sales",J1:K2),"Check matching records")

Expected output for South/A is 500. The label is a chosen reporting convention and should not replace a detailed count in an audit workflow.

In a separate copy, change all three North/A Sales values to 100. There are still three matching records. The documented multiple-match condition still applies even though the candidate returned values are identical.

DGET tests uniqueness of the matching record selection, not uniqueness of the distinct values in the requested field. Deduplicating result values would silently change that requirement.

The dedicated help defines no-match and multiple-match behavior but does not settle every single-match blank, formula-error or coercion case. Those outcomes are not asserted here. Keep the source row available for inspection, especially when retrieving values used for consequential decisions.

Use the complete header-and-condition range, not just the condition cells. Check the field heading for spelling and spacing, and verify that a numeric field index still points to the intended column after a layout change. A worksheet’s visible rows are not a substitute for the explicit database range and criteria used in the formula.

Do not include an accidental empty conditions row in the criteria range: an unrestricted alternative can broaden selection. Microsoft’s DAVERAGE reference documents the shared AND/OR, exact-text and case-insensitive matching rules. These examples avoid wildcard, case-sensitive-formula and numeric-text complications.

The database functions do not validate whether the selected records are the right records for the question. Keep a matching-record or numeric-field count beside an important result. Native blank/formula-empty handling, coercion, extreme precision and error propagation not explicitly documented in this guide remain unverified.

• Current documentation: the dedicated Microsoft reference lists Excel for Microsoft 365, Excel for Microsoft 365 for Mac, Excel 2024, Excel 2024 for Mac, Excel 2021, Excel 2021 for Mac, Excel 2019, Excel 2016.
• Introduced: the first introduction version/build is not established by the dedicated reference used here.
• Last functioning version: unknown; a current help listing is not a last-working-build test.
• Evidence checked: October 15, 2026. Official documentation was freshly retrieved; examples were independently calculated. No native Excel, Mac or web-client execution was performed.

• Microsoft DGET reference.
• Microsoft function catalog and introduction-marker explanation.
• Editorial testing standards.

Examples use English function names and comma separators. Expected numbers are rounded independent mathematical calculations; documented error expectations are labeled as such. They are not captured Excel output. All illustrative data and policies are fictional.