Browse

DGET Function (LibreOffice Calc)

Database Advanced LibreOffice Calc LibreOffice 3.0

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.

The **DGET** function retrieves a **single value** from a database column when **exactly one record** matches the criteria. If zero or multiple records match, DGET returns an error. This makes it ideal for precise lookups, unique-key retrieval, and validation of dataset uniqueness.

This guide provides syntax, examples, edge cases, and best practices, making it one of the most complete DGET references available for LibreOffice Calc.

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
DGET is your precision lookup tool — perfect for retrieving a single, uniquely identified record with clean, structured criteria.
  • 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.