Worksheet function "DGET"

:Yes it does. Or at least Office XP does, it's help provided me with enough info to be able to figure it out. But i'll help anyway.

Hide All

DGET
See Also

Extracts a single value from a column of a list or database that matches conditions you specify.

Syntax

DGET(database,field,criteria)

Database is the range of cells that makes up the list or database. A database is a list of related data in which rows of related information are records, and columns of data are fields. The first row of the list contains labels for each column.

Field indicates which column is used in the function. Field can be given as text with the column label enclosed between double quotation marks, such as "Age" or "Yield," or as a number that represents the position of the column within the list: 1 for the first column, 2 for the second column, and so on.

Criteria is the range of cells that contains the conditions you specify. You can use any range for the criteria argument, as long as it includes at least one column label and at least one cell below the column label for specifying a condition for the column.

Remarks

If no record matches the criteria, DGET returns the #VALUE! error value.
If more than one record matches the criteria, DGET returns the #NUM! error value.
Example

The example may be easier to understand if you copy it to a blank worksheet.

How?

Create a blank workbook or worksheet.
Select the example in the Help topic. Do not select the row or column headers.

Selecting an example from Help

Press CTRL+C.
In the worksheet, select cell A1, and press CTRL+V.
To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Tools menu, point to Formula Auditing, and then click Formula Auditing Mode.


1
2
3
4
5
6
7
8
9
10
A B C D E F
Tree Height Age Yield Profit Height
Apple >10 <16
Pear
Tree Height Age Yield Profit
Apple 18 20 14 105.00
Pear 12 12 10 96.00
Cherry 13 14 9 105.00
Apple 14 15 10 75.00
Pear 9 8 8 76.80
Apple 8 9 6 45.00
Formula Description (Result)
=DCOUNT(A4:E10,"Age",A1:F2) This function looks at the records of apple trees between a height of 10 and 16 and counts how many of the Age fields in those records contain numbers. (1)
=DCOUNTA(A4:E10,"Profit",A1:F2) This function looks at the records of apple trees between a height of 10 and 16 and counts how many of the Profit fields in those records are not blank. (1)
=DMAX(A4:E10,"Profit",A1:A3) The maximum profit of apple and pear trees. (105)
=DMIN(A4:E10,"Profit",A1:B2) The minimum profit of apple trees over 10 in height. (75)
=DSUM(A4:E10,"Profit",A1:A2) The total profit from apple trees. (225)
=DSUM(A4:E10,"Profit",A1:F2) The total profit from apple trees with a height between 10 and 16. (75)
=DPRODUCT(A4:E10,"Yield",A1:B2) The product of the yields from apple trees with a height greater than 10. (140)
=DAVERAGE(A4:E10,"Yield",A1:B2) The average yield of apple trees over 10 feet in height. (12)
=DAVERAGE(A4:E10,3,A4:E10) The average age of all trees in the database. (13)
=DSTDEV(A4:E10,"Yield",A1:A3) The estimated standard deviation in the yield of apple and pear trees if the data in the database is only a sample of the total orchard population. (2.97)
=DSTDEVP(A4:E10,"Yield",A1:A3) The true standard deviation in the yield of apple and pear trees if the data in the database is the entire population. (2.65)
=DVAR(A4:E10,"Yield",A1:A3) The estimated variance in the yield of apple and pear trees if the data in the database is only a sample of the total orchard population. (8.8)
=DVARP(A4:E10,"Yield",A1:A3) The true variance in the yield of apple and pear trees if the data in the database is the entire orchard population. ( 7.04)
=DGET(A4:E10,"Yield",A1:A3) Returns the #NUM! error value because more than one record meets the criteria.

Tips

You can use any range for the criteria argument, as long as it includes at least one column label and at least one cell below the column label for specifying the condition.
For example, if the range G1:G2 contains the column label Income in G1 and the amount 10,000 in G2, you could define the range as MatchIncome and use that name as the criteria argument in the database functions.

Although the criteria range can be located anywhere on the worksheet, do not place the criteria range below the list. If you add more information to the list by using the Form command on the Data menu, the new information is added to the first row below the list. If the row below the list is not blank, Microsoft Excel cannot add the new information.
Make sure the criteria range does not overlap the list.
To perform an operation on an entire column in a database, enter a blank line below the column labels in the criteria range.
Examples of criteria

Multiple conditions in a single column

If you have two or more conditions for a single column, type the criteria directly below each other in separate rows. For example, the following criteria range displays the rows that contain either "Davolio," "Buchanan," or "Suyama" in the Salesperson column.

Salesperson
Davolio
Buchanan
Suyama

One condition in two or more columns

To find data that meets one condition in two or more columns, enter all the criteria in the same row of the criteria range. For example, the following criteria range displays all rows that contain "Produce" in the Type column, "Davolio" in the Salesperson column, and sales values greater than $1,000.

Type Salesperson Sales
Produce Davolio >1000

One condition in one column or another

To find data that meets either a condition in one column or a condition in another column, enter the criteria in different rows of the criteria range. For example, the following criteria range displays all rows that contain either "Produce" in the Type column, "Davolio" in the Salesperson column, or sales values greater than $1,000.

Type Salesperson Sales
Produce
Davolio
>1000

One of two sets of conditions for two columns

To find rows that meet one of two sets of conditions, where each set includes conditions for more than one column, type the criteria in separate rows. For example, the following criteria range displays the rows that contain both "Davolio" in the Salesperson column and sales values greater than $3,000, and also displays the rows for salesperson Buchanan with sales values greater than $1,500.

Salesperson Sales
Davolio >3000
Buchanan >1500

More than two sets of conditions for one column

To find rows that meet greater than two sets of conditions, include multiple columns with the same column heading. For example, the following criteria range displays sales that are between 5,000 and 8,000 in addition to sales that are less than 500.

Sales Sales
>5000 <500

Conditions created as the result of a formula

You can use a calculated value that is the result of a formula (formula: A sequence of values, cell references, names, functions, or operators in a cell that together produce a new value. A formula always begins with an equal sign (=).)as your criterion. When you use a formula to create a criterion, do not use a column label for a criteria label; either keep the criteria label blank or use a label that is not a column label in the list. For example, the following criteria range displays rows that have a value in column C greater than the average of cells C7:C10.

=C7>AVERAGE($C$7:$C$10)

Notes

The formula you use for a condition must use a relative reference (relative reference: In a formula, the address of a cell based on the relative position of the cell that contains the formula and the cell referred to. If you copy the formula, the reference automatically adjusts. A relative reference takes the form A1.) to refer to the column label (for example, Sales) or the corresponding field in the first record. All other references in the formula must be absolute references (absolute cell reference: In a formula, the exact address of a cell, regardless of the position of the cell that contains the formula. An absolute cell reference takes the form $A$1.), and the formula must evaluate to TRUE or FALSE. In the formula example, "C7" refers to the field (column C) for the first record (row 7) of the list.

You can use a column label in the formula instead of a relative cell reference or a range name. When Microsoft Excel displays an error value such as #NAME? or #VALUE! in the cell that contains the criterion, you can ignore this error because it does not affect how the list is filtered.

When evaluating data, Microsoft Excel does not distinguish between uppercase and lowercase characters.

Does anyone have any concrete examples of using this method of defining a DGET operation:

"Conditions created as the result of a formula"

I can't get it to work the way that they have described in the help file.

Thanks,
Shishir