DGET Function (LibreOffice Calc)
The DGET function retrieves a single value from a database column when exactly one record matches the criteria. It is part of the database function family and supports structured, criteria-based filtering.
Compatibility
▾| Excel | ✔ |
| Gnumeric | ✔ |
| Google_sheets | ✔ |
| Libreoffice | ✔ |
| Numbers | ✖ |
| Onlyoffice | ✔ |
| Openoffice | ✔ |
| Wps | ✔ |
| Zoho | ✔ |
What the DGET Function Does â–¾
- Retrieves a single value from a specified column
- Requires exactly one matching record
- Returns an error if zero or multiple matches exist
- Uses structured criteria ranges
- Supports AND/OR logic via criteria layout
It is designed to be precise, strict, and ideal for unique-record lookups.
Syntax â–¾
DGET(database; field; criteria)
Arguments
-
database:
A range where the first row contains column labels. -
field:
The column to retrieve from.- Use column label in quotes (recommended)
- Or use column index (1 = first column)
-
criteria:
A range containing column labels and one or more criteria rows.
Basic Examples â–¾
Assume a table in A1:C6:
| A (Name) | B (Age) | C (Score) |
|---|---|---|
| John | 25 | 80 |
| Mary | 30 | 90 |
| Alex | 22 | 70 |
| John | 28 | 85 |
| Mary | 35 | 88 |
Criteria in E1:E2:
| Name |
|---|
| Alex |
Retrieve Score for Alex
=DGET(A1:C6; "Score"; E1:E2)
Returns 70.
Retrieve Age for Alex
=DGET(A1:C6; "Age"; E1:E2)
Returns 22.
Advanced Examples â–¾
Retrieve Score where Name = “Mary” AND Age = 30
Criteria:
| Name | Age |
|---|---|
| Mary | 30 |
Formula:
=DGET(A1:C6; "Score"; E1:F2)
Returns 90.
Retrieve record using OR logic (must still result in one match)
Criteria:
| Name |
|---|
| Alex |
| John |
If only one of these names exists in the dataset, DGET works.
If both exist → #NUM! (multiple matches).
Retrieve using field index
=DGET(A1:C6; 3; E1:E2)
Retrieves Score.
Retrieve using wildcard criteria
Criteria:
| Name |
|---|
| A* |
If only one name begins with A, DGET returns it.
Retrieve using dynamic criteria
=DGET(A1:C6; "Score"; H1:I2)
Where H1:I2 is generated by formulas.
Retrieve unique ID from a structured table
=DGET(A1:D100; "ID"; F1:G2)
Retrieve value from a unique-key column
=DGET(A1:C6; "Age"; E1:E2)
Edge Cases and Behavior Details â–¾
DGET requires exactly one matching record
- 0 matches →
#VALUE! -
1 match →
#NUM!
DGET retrieves text, numbers, or logical values
field can be:
- Column label
- Column index
- Cell containing label
criteria must include column labels
Exact match required.
criteria supports:
- Comparison operators
- Wildcards
- Multiple rows (OR)
- Multiple columns (AND)
DGET of an error in database → error ignored unless matched row contains error
DGET of an error in criteria → error returned
Empty criteria → error (ambiguous)
DGET is case-insensitive for text matching
Common Errors and Fixes â–¾
#NUM! — Multiple matches
Cause:
- Criteria too broad
- Duplicate records
- OR logic returns more than one row
Fix:
- Add more criteria
- Ensure unique keys
#VALUE! — No matches
Cause:
- Criteria too strict
- Typo in criteria
- Criteria labels don’t match database labels
Fix:
- Relax criteria
- Correct labels
- Verify data
Err:502 — Invalid argument
Occurs when:
- field is invalid
- database range malformed
Criteria not applied
Cause:
- Criteria labels not identical to database labels
Best Practices â–¾
- Use DGET for unique-record lookups
- Use DGET to validate uniqueness in datasets
- Use column labels instead of index numbers
- Keep criteria ranges small and clearly labeled
- Use AND logic for precise filtering
- Use OR logic only when uniqueness is guaranteed
- Use FILTER (modern Calc) when multiple matches are expected
Related Patterns and Alternatives â–¾
- Use DMAX and DMIN for extrema
- Use DSUM and DAVERAGE for aggregation
- Use DCOUNT or DCOUNTA for counting
- Use VLOOKUP, XLOOKUP, or INDEX/MATCH for non-database lookups
- Use FILTER for multi-record extraction
By mastering DGET and its companion database functions, you can build powerful, structured, and criteria-driven lookup workflows in LibreOffice Calc.