The DGET function in Excel is used to extract a single value from a database (a range of cells that includes headers) that matches specified criteria. It’s useful when you need to retrieve data based on multiple conditions. Here’s how to use DGET:
Syntax
DGET(database, field, criteria)
- `database`: The range of cells that makes up the database. The top row of this range should contain the headers.
- `field`: The column from which you want to retrieve data. It can be the column label (in quotes) or the column index number.
- `criteria`: The range of cells that contains the conditions you specify. This range must include at least one column label and the corresponding condition(s) below it.
Steps to Use DGET
- Prepare Your Data: Ensure your data is laid out in a tabular format with header labels in the first row.
- Set Up Criteria: Create a criteria range on your worksheet. This range should include at least one header label that matches a column in your database, and at least one condition below that header.
- Enter the DGET Function: Click on the cell where you want the result to appear. Enter the function using the format `DGET(database, field, criteria)`.
Example
Suppose you have the following database in range `A1:C5`:
| Name | Age | City |
|——–|—–|———–|
| Alice | 24 | New York |
| Bob | 30 | Los Angeles |
| Charlie| 24 | New York |
| David | 35 | Chicago |
And you want to find the age of the person named “Alice”:
Name
Alice
- Database: `A1:C5`
- Field: `”Age”` (or you could use the column index `2`)
- Criteria:
Located in cells `E1:E2`.
- Formula: In the cell where you want the result, enter `=DGET(A1:C5, “Age”, E1:E2)`
Notes
- The DGET function will return an error if:
- No record matches the criteria.
- More than one record matches the criteria.
- Criteria can be more complex by having multiple columns and rows, essentially acting like an “AND” operation across the rows.
By setting up your criteria properly, DGET can be an effective way to pull specific information from a large dataset based on multiple conditions.
Unlock Your Potential
Excel
Access
Power BI
