The DGET function in Excel is a useful tool for extracting a single value from a column of a database that matches specified criteria. While INDEX-MATCH and XLOOKUP are more versatile for looking up values, DGET can be simpler for straightforward scenarios, particularly when working with structured data. Here’s how you can use DGET instead of INDEX-MATCH or XLOOKUP:
DGET Function Syntax
- database: The range of cells that makes up the database, including headers.
- field: The column from which you want to retrieve the data. This can be specified by either the column number (1 for the first column, 2 for the second, etc.) or by the header name in quotes.
- criteria: The range of cells that contains the conditions to filter the data.
Example Usage
Suppose you have the following data in a table (A1):
| Name | Age | City |
|---|---|---|
| Alice | 30 | New York |
| Bob | 25 | Los Angeles |
| Charlie | 35 | Chicago |
| David | 28 | New York |
| Eve | 22 | Chicago |
If you want to retrieve the age of the person named “David,” you can set up your criteria like this (E1):
| Name |
|---|
| David |
And then use the DGET function like this:
Key Points
- Single Value Return: DGET returns a single value that matches the criteria. If multiple matches exist, or if no matches are found, it returns an error.
- Structured Reference: If your data is formatted as a table, you can use structured references which make it easier to read.
- Simpler Syntax: For simple lookups based on one criterion, DGET can be more straightforward than using INDEX-MATCH or XLOOKUP.
Limitations
- DGET is less flexible than INDEX-MATCH or XLOOKUP when it comes to multi-criteria lookups or more complex conditions.
- It can only return a single value, which can be a limitation if you need to return multiple results.
Using DGET can be a good option when you have a clear, simple case where you need to retrieve data from a structured range. For more complex queries, consider sticking with INDEX-MATCH or XLOOKUP.