DGET Function Overview
The DGET function is one of Excel's database functions.
This group of functions is designed to make it easy to summarize information from large tables of data.
They do this by returning specific information based on one or more criteria chosen by the user.
The DGET function can be used to return a single field of data from a column of a database that matches conditions that you specify.
DGET Syntax and Arguments
The syntax for the DGET function is:
= DGET ( database, field, criteria )All database functions have the same three arguments:
Database: (required) Specifies the range of cell references containing the database. The field names must be included in the range.
Field: (required) Indicates which column or field is to be used by the function in its calculations. Enter the argument either by typing the field name - such as #Orders - or enter the column number - such as 3.
Criteria: (required) Lists the range of cells containing the conditions specified by the user. The range must include at least one field name from the database and at least one other cell reference indicating the condition to be evaluated by the function.
Example Using Excel's DGET Function - Matching a Single Criterion
Click on the image above for a larger view of this example.
This example will use DGET to find the number of sales orders placed by a specific sales agent for a given month.
Entering the Tutorial Data
Note: The tutorial does not include formatting steps. Information on worksheet formatting options is available in this Basic Excel Formatting Tutorial.
- Enter the data table as seen in the image above into cells D1 to F13
- Leave cell E5 blank - this is where the DGET formula will be located
- The field names in cells D2 to F2 will be used as part of the function's Criteria argument
Selecting the Criteria
To get DGET to only look at data for a specific sales rep we enter the name of an agent under the SalesRep field name in row 3.
- In cell F3 type the criteria Harry
- In cell E5 type the heading #Orders: to indicate the information we will be finding with DGET
Naming the Database
Using a named range for large ranges of data such as a database can not only make it easier to enter this argument into the function, but it can also prevent errors caused by selecting the wrong range.
Named ranges are very useful if you use the same range of cells frequently in calculations or when creating charts or graphs.
- Highlight cells D7 to F13 in the worksheet to select the range
- Click on the name box above column A in the worksheet
- Type SalesData into the name box to create the named range
- Press the Enter key on the keyboard to complete the entry
Opening the DGET Dialog Box
A function's dialog box provides an easy method for entering data for each of the function's arguments.
Opening the dialog box for the database group of functions is done by clicking on the function wizard button (fx) located next to the formula bar above the worksheet - see image above.
- Click on cell E5 - the location where the results of the function will be displayed
- Click on the function wizard button (fx) to bring up the Insert Function dialog box
- Type DGET in the Search for a function window at the top of the dialog box
- Click on the GO button to search for the function
- The dialog box should find DGET and list it in the Select a function window
- Click OK to open the DGET function dialog box
Completing the Arguments
- Click on the Database line of the dialog box
- Type the range name SalesData into the line
- Click on the Field line of the dialog box
- Type the field name #Orders into the line
- Click on the Criteria line of the dialog box
- Highlight cells D2 to F3 in the worksheet to enter the range
- Click OK to close the DGET function dialog box and complete the function
- The answer 217 should appear in cell E5 as this is the number of sales orders placed by Harry this month
- When you click on cell E5 the complete function
=DGET(SalesData, "#Orders", D2:F3) appears in the formula bar above the worksheet
Database Function Errors
#Value: Occurs most often when the field names were not included in the database argument.
For the example above, be sure that the field names in cells D6:F6 were included in the named range SalesData.