DGET

DGET(database, field, criteria)

Returns the single value in a database column satisfying certain conditions.

database	The range of cells that make up the database or list.
field	The column name or number indicating which column to use.
criteria	The range of cells that contain the conditions.

REMARKS

* For an illustrated example refer to the Specifying Database Criteria page.
* Any cells containing text, logical values or errors are NOT INCLUDED. See Example 15.
* If more than one row meets the criteria, then #NUM! is returned. See Examples 11 and 12.
* The "database" can be a cell reference or a named range.
* The "database" range of cells must contain column headings in the first row. See Example 19.
* The "field" can be a column label, a column number (left to right) or a cell reference.
* The "field" label is not case sensitive. See Example 9.
* If "field" is left blank, then #VALUE! is returned. See Example 20.
* The "criteria" must include at least one column heading and one cell below the column heading. See Example 16.
* If "criteria" is left blank, then #VALUE! is returned. See Example 21
* The "criteria" must be in a contiguous block of adjacent cells.
* The "criteria" can be anywhere on the active sheet, although it is recommended not to put it below the data, in case more data is added later on.
* Make sure the "criteria" and "database" ranges do not overlap and that they are always separated by at least one blank row or column.
* To perform an operation on an entire column in a database, enter a blank line below the column labels in the "criteria" range.
* If no rows match the criteria, then #VALUE! is returned. See Example 14.
* It is possible to use the data from a pivot table. When the "database" range refers to a cell inside a pivot table, the calculation is only performed on the data currently displayed in the pivot table.
* For the Microsoft documentation refer to support.microsoft.com
* For the Google documentation refer to support.google.com

	A	B	C	D
1	=DGET(B1:C10,"Age",B12:B13) = 19	Name	Age	Salary
2	=DGET(B1:C10,2,B12:B13) = 19	John	19	18,000
3	=DGET(B1:D10,"Salary",B12:B13) = 18,000	Mark	20	24,500
4	=DGET(B1:D10,"Age",B16:B17) = 22	David	17	16,500
5	=DGET(B1:D10,"Name",D16:D17) = James	James	22	#NAME?
6	=DGET(B1:D10,"Name",C12:D13) = Mark		18	19,500
7	=DGET(B1:D7,"Name",C12:C14) = Mark	Nick	21	24,000
8	=DGET(B1:D2,"Name",D12:D13) = John	Matthew	text	26,500
9	=DGET(B1:D10,"Name",C16:C17) = Jamie	Jamie	16	18,500
10	=DGET(B1:D10,"NAME",C16:C17) = Jamie	Mark	24	29,000
11	=DGET(B1:D10,"Name",D12:D14) = #NUM!
12	=DGET(B1:D10,"Age",B12:B14) = #NUM!	Name	Salary	Salary
13	=DGET(B1:D10,"Age",D14) = #VALUE!	John	>24000	<25000
14	=DGET(B2:D10,"Age",B12:B13) = #VALUE!	David	<12000	>18000
15	=DGET(B1:D10,"Salary",D16:D17) = #NAME?
16	=DGET(B1:D10,"Age",C12) = #VALUE!	Name	Age	Age
17	=DGET(B1:D1,"Age",B12:B14) = #VALUE!	James	<17	22
18	=DGET(B1:D10,"Salary",B19) = #VALUE!
19	=DGET(B2:D10,"Age",B12:B13) = #VALUE!
20	=DGET(B1:D10,,C12:D13) = #VALUE!
21	=DGET(B1:D10,"Age",) = #VALUE!
22	=DGET(B1:D10,"some text",B12:B13) = #VALUE!

1 - What is the "Age" of the person with the name "John" using a field label.
2 - What is the "Age" of the person with the name "John" using a field column.
3 - What is the "Salary" of the person with the name "John".
4 - What is the "Age" of the person with the name "James".
5 - What is the "Name" of the person who is 22 years old.
6 - What is the "Name" of the person with a salary between £24,000 and £25,000.
7 - What is the "Name" of the person with a salary between £12,000 and £24,000.
8 - What is the "Name" of the person with a salary less than £25,000.
9 - What is the "Name" of the person who is less than 18 years old.
10 - This is the same as 8 but with the field label in uppercase.
11 - What is the "Name" of the person with a salary between £18,000 and £25,000.
12 - What is the "Age" of the person with either the name "John" or the name "David".
13 - If the "criteria" refers to a range of empty cells, then #VALUE! is returned.
14 - If the "criteria" you specify does not return any rows, then #VALUE! is returned.
15 - If the matching value is an error value, then the corresponding error value is returned.
16 - If the "criteria" only includes a column heading, then #VALUE! is returned.
17 - If the "database" range only includes column headings, then #VALUE! is returned.
18 - If the "criteria" refers to a single empty cell, then #VALUE! is returned.
19 - If the "database" range does not include column headings, then #VALUE! is returned.
20 - If "field" is left blank, then #VALUE! is returned.
21 - If "criteria" is left blank, then #VALUE! is returned.
22 - If "field" does not match one of the column headings, then #VALUE! is returned.