Excel functions / Database
DGET function
The one value in a column of a table from the single row that meets the conditions. Two matching rows are an error, which makes it a strict lookup.
Syntax
=DGET(database, field, criteria)
Arguments
database: The table, with its column labels in the first row.field: The column to work on: its label in quotes ("Profit") or its number counting from 1.criteria: The conditions: a range with column labels in its first row and what to match below them.
When to use it
DGET reads a single value from a table: the one in the row that meets the conditions written in a criteria range. It is a lookup that insists on exactly one match.
That strictness is the point. Where VLOOKUP quietly returns the first of two matches, DGET stops with an error, which is how you find out that a code is in the list twice.
Examples
The price of one product
A B C D E 1 Code 2 P-102 1.2 3 4 Code Product Price 5 P-101 Notebook 3.5 6 P-102 Pen 1.2 7 P-103 Stapler 8.9 8 P-103 Stapler (old) 7.5 In E2:
=DGET(A4:C8, "Price", A1:A2)→ 1.2A code that is there twice
A B C D E 1 Code 2 P-103 #NUM! 3 4 Code Product Price 5 P-101 Notebook 3.5 6 P-102 Pen 1.2 7 P-103 Stapler 8.9 8 P-103 Stapler (old) 7.5 In E2:
=DGET(A4:C8, "Price", A1:A2)→ #NUM!Two rows have the code P-103. DGET refuses to choose.
A code that is not there
A B C D E 1 Code 2 P-999 #VALUE! 3 4 Code Product Price 5 P-101 Notebook 3.5 6 P-102 Pen 1.2 7 P-103 Stapler 8.9 8 P-103 Stapler (old) 7.5 In E2:
=DGET(A4:C8, "Price", A1:A2)→ #VALUE!
Common mistakes
- Reading the two errors. #NUM! means more than one row matched; #VALUE! means none did.
- Text matches the start. A condition of P-10 would match P-101 and P-102, and give #NUM!. Type ="=P-10" for an exact match.
DGET in other languages
Excel shows function names in its own language. In these languages it also puts a semicolon between arguments where English has a comma.
- Czech: DZÍSKAT
- Danish: DHENT
- Dutch: DBLEZEN
- Finnish: TNOUDA
- French: BDLIRE
- German: DBAUSZUG
- Hungarian: AB.MEZŐ
- Italian: DB.VALORI
- Norwegian: DHENT
- Polish: BD.POLE
- Portuguese (Brazil): BDEXTRAIR
- Portuguese (Portugal): BDOBTER
- Russian: БИЗВЛЕЧЬ
- Spanish: BDEXTRAER
- Swedish: DHÄMTA
- Turkish: VAL
Related functions
- DSUM: Adds up a column of a table for the rows that meet conditions written in cells.
- XLOOKUP: Searches a range and returns the matching item from another range. The modern replacement for VLOOKUP.
- VLOOKUP: Finds a value in the first column of a table and returns a value from the same row.
- INDEX: The value at a given row and column of a range.
- FILTER: Keeps only the rows that meet a condition. The result spills.