The DGET function returns a single value from a column of a list or database that matches conditions that you specify.

Syntax

=DGET(database,field,criteria)

Arguments

Argument Description
database The range of cells that makes up the list or database
field Indicates which column is used in the function

  This can either be a field number, or can be the field name, i.e. the header in the top row of the database, encased in quotes, e.g. “Gender”, “Subject”, etc.
criteria A range of cells that contain the criteria, to specify which record should be returned

  The range can include one or more criteria, which are presented as a field name in one cell and the condition for that field in the cell below, e.g.

Gender Age
Male >10

Note: The criteria supplied beneath each field heading can be either:

  • a numeric value, including an integer, decimal, date, time, or logical value, e.g. 10, 01/01/2010, FALSE
  • a text string, e.g. “Name”, “Tuesday”
  • an expression, e.g. “>10”, “<>0”).

The Excel database functions are not case sensitive. So, for example, the criteria =”Math” will be satisfied by cells containing the text “Math” or “math”.

Examples

  A B C D E F G H
1 Quarter Region Agent Sales   Agent Quarter  
2 1 North Norton $222,100   Norton 4  
3 1 North Sam $252,280        
4 1 East Sarah $222,100        
5 2 North Norton $251,280        
6 2 South Sam $229,785        
7 2 East Sarah $232,190        
8 3 North Norton $294,820        
9 3 South Sam $273,139        
10 3 East Sarah $219,167        
11 4 North Norton $258,142        
12 4 South Sam $265,180        
13 4 East Sarah $266,132        
14                
15 Formula Result Notes
16 =DGET(A1:D13,”Sales”,F1:G2) $258,142 Function finds the record in row 11, and returns the value from the “Sales” column.

Note: You can also use the following wildcards in text-related criteria arguments:

      ?  –  matches any single character
  *  –  matches any sequence of characters

e.g. the condition “A*e” will match any substring beginning with “A” and ending in “e”.

  • If you do actually want to find the ? or * character, type the ~ symbol before this character in your search.

Common Function Error(s)

Problem What went wrong
#VALUE! Occurs if no records in the supplied database satisfy the supplied criteria
#NUM! Occurs if more than one record in the supplied database satisfies the supplied criteria