DGET function. Get the value by specifying the item and search criteria.(Microsoft Excel)
Get the value by specifying the item and search criteria.
However, this function will give a #NUM! error if the search does not yield a single result.
Since Excel 2019, an easy-to-use FILTER function has been added, and the FILTER function is recommended.
What is a database in Excel functions?
A database is a table with item names in the first row and rows of data underneath.
It is a database in the Excel functions.
How it works
=DGET(Database,Field,Criteria)
| Argument | Omission | Specify |
|---|---|---|
| Database | Required argument. | Determination target. Specify the range of the table containing the item name. In the aforementioned sample, A2 to C13. |
| Field | Required argument. | Field to be retrieved. |
| Criteria | Required argument. | Conditions to be retrieved. |
Example Results
One condition. Use column name for Field
The following are specified as arguments.
- Target table range in Database.
- "Price" and "Qty" as Field.
- Criteria is that "Name" must be "C".
As a result, "Price" of "C" is displayed in E5 and "Qty" in E6.
Only one result of the function is normally displayed.
If there are multiple results, a #NUM! error will occur.
One condition.Use column number for Field
Items can be numbered consecutively with the leftmost number being 1.
In most cases, item names are recommended because they make the formulas more readable.
However, if the item names change frequently, column numbers are better.
Multiple condition specification (logical OR).
Arranging the conditions vertically results in a logical sum.
In cell E6 of the example below, the rows corresponding to "C" and "D" are retrieved.
This usage is also impractical because multiple results will result in a #NUM! error.
Multiple condition specification (logical AND).
The conditions are arranged horizontally to form an "AND" condition.
In the example below, "Name" is "C" and "Price" is 82 or more, and the "Qty" of the corresponding row is obtained.
Multiple condition specification. (Range of values)
A range of conditions can also be specified, such as "greater than or equal to" or "less than or greater than.
To do so, specify a comparison operator with the same items side by side.
---