fx
FunctionPedia
Quick Answer

DGET Function

Pulls one value from a database column when exactly one row meets the criteria.

✓ Excel✓ Google SheetsExcel All versions

Syntax

DGET
(database, field, criteria)

Parameters

ParameterDescriptionRequired
databaseThe range containing the database, including the header row as the first row.Required
fieldThe column to extract from, given as the column label in quotes (e.g. "Yield") or its 1-based column index.Required
criteriaThe range with a header row matching database column names and exactly one condition row that uniquely identifies a single record.Required

Basic Example

Extract the Yield of the single cherry tree

=DGET(A1:E7, "Yield", A9:A10)
Result9

The criteria A9:A10 (Tree=Cherry) matches exactly one record, so DGET returns its Yield of 9.

Advanced Examples

Example 1: Multiple matches cause #NUM!

Criteria that matches more than one row

Using Tree=Apple, Height>10 matches two rows, which is ambiguous for a single-value extract.

=DGET(A1:E7, "Yield", A9:B10)
Result: #NUM!
DGET found more than one matching record, so it returns #NUM! because it cannot choose one.

Example 2: No match causes #VALUE!

Criteria that matches nothing

Using Tree=Banana, which does not exist in the database.

=DGET(A1:E7, "Yield", A9:A10)
Result: #VALUE!
No record satisfied the criteria, so DGET returns #VALUE!.

How DGET Works

DGET filters the database to rows matching the criteria and then checks how many records remain. If exactly one remains, it returns the value from the specified field. If zero remain it returns #VALUE!; if two or more remain it returns #NUM!.

1
Set up the database
Enter your table with a header row, e.g. A1:E7 with Tree, Height, Age, Yield, Profit.
2
Set up a unique criteria
Create a criteria range that matches exactly one record, e.g. A9:A10 with Tree and Cherry.
3
Enter DGET
Type =DGET(A1:E7, "Yield", A9:A10) and press Enter.

Important Notes & Limitations

  • Requires the criteria to match exactly one record; it is not meant for aggregating multiple rows.

  • Returns #NUM! when more than one record matches and #VALUE! when none match.

  • Criteria header labels must exactly match database headers.

Common Errors & Fixes

#NUM!More than one record satisfied the criteria, so the result is ambiguous.

Fix: Tighten the criteria so only one record matches, or use DSUM/DMAX/etc. for aggregates.

#VALUE!No record matched the criteria.

Fix: Verify the criteria values and header spelling match the database.

Download Practice File

Practice DGET with Real Data

Download a sample CSV file with pre-populated data and practice exercises for the DGET function. Works in both Excel and Google Sheets.

Works in Google SheetsCompatible with ExcelIncludes exercises

File format: CSV (comma-separated values) - opens in Excel, Google Sheets, and all spreadsheet apps

Frequently Asked Questions

Why did DGET return #NUM!?
Because more than one record matched your criteria. DGET can only return a value when exactly one record matches.
Why did DGET return #VALUE!?
Because no record matched your criteria at all.
How do I get a value when several rows match?
Use an aggregate D-function such as DSUM, DMAX, or DAVERAGE instead of DGET.