DGET Function in Excel

Part 1: Introduce

Definition: The DGET function in Excel is a database function. It extracts a single record from a database that matches the specified conditions.

Purpose: The DGET function is used when you need to find a specific piece of data in a database based on multiple criteria.

Syntax & Arguments: The syntax for the DGET function in Excel is:

syntax

=DGET(database, field, criteria)

Explain the Arguments in the function:

database: The range of cells that make up the database. The database should include column headers.
field: The process will use the column to find and return the result. You can reference the area by its name (header name) or column number in the database.
criteria: The range of cells that contain the conditions you specify. It includes at least one column header and at least one cell below the column header for setting conditions for the corresponding column.

Return value: The DGET function will return the field value for the first record in the database that matches the criteria.

Remarks: If no records match the criteria, DGET we will produce an #VALUE! Error. If more than one record matches the criteria, DGET it will have a #NUM! Error.

Part 2: Examples

Example 1:

Purpose of illustration: Find the age of a specific employee named “John”.
Data tables and formulas:

	A	B	C
1	Name	Age	Formula
2	John	32	`=DGET($A$1:$B$4, "Age", $E$1:$E$2)`
3	Anna	27
4	Rachel	45

Where E1:E2 is the criteria range:

	E
1	Name
2	John

Result table:

	A	B	C
1	Name	Age	Result
2	John	32	32
3	Anna	27	#VALUE!
4	Rachel	45	#VALUE!

Explanation: The formula uses the DGET Function to look for the name “John” in the “Name” column and then return his age from the “Age” column. The formula is inserted into the “Result” column.

Example 2:

Purpose of example: Find the sales of a product named “Product B” in the “East” region.
Data tables and formulas:

	A	B	C	D
1	Product	Region	Sales	Formula
2	Product A	West	1500	`=DGET($A$1:$C$4, "Sales", $E$1:$F$2)`
3	Product B	East	2200
4	Product C	South	1850

Where E1:F2 is the criteria range:

	E	F
1	Product	Region
2	Product B	East

Result table:

	A	B	C	D
1	Product	Region	Sales	Result
2	Product A	West	1500	#VALUE!
3	Product B	East	2200	2200
4	Product C	South	1850	#VALUE!

Explanation: The formula looks for “Product B” in the “East” region in the database, then returns its sales from the “Sales” column. The procedure is inserted into the “Result” column.

Example 3:

Purpose of example: Find an employee’s salary with the job title “Manager”.
Data tables and formulas:

	A	B	C	D
1	Employee	Job	Salary	Formula
2	Alice	Manager	60000	`=DGET($A$1:$C$4, "Salary", $E$1:$F$2)`
3	Bob	Analyst	50000
4	Charlie	Manager	65000

Where E1:F2 is the criteria range:

	E	F
1	Employee	Job
2	Charlie	Manager

Result table:

	A	B	C	D
1	Employee	Job	Salary	Result
2	Alice	Manager	60000	#VALUE!
3	Bob	Analyst	50000	#VALUE!
4	Charlie	Manager	65000	65000

Explanation: The formula looks for an employee named “Charlie” with the job title “Manager”, then returns his salary from the “Salary” column. The formula is inserted into the “Result” column.

Example 4:

Purpose of example: Use DGET with the IF function to find the total profit of a product. If the gain is not available, return “Profit not available.”
Data tables and formulas:

	A	B	C	D
1	Product	Cost	Price	Formula
2	A	15	20	`=IF(DGET($A$1:$C$4, "Price", $E$1:$F$2)-DGET($A$1:$C$4, "Cost", $E$1:$F$2)=0,"Profit not available",DGET($A$1:$C$4, "Price", $E$1:$F$2)-DGET($A$1:$C$4, "Cost", $E$1:$F$2))`
3	B	20	25
4	C	25	30

Where E1:F2 is the criteria range:

	E
1	Product
2	A

Result table:

	A	B	C	D
1	Product	Cost	Price	Profit
2	A	15	20	5
3	B	20	25	#VALUE!
4	C	25	30	#VALUE!

Explanation: The formula calculates the profit for product A. If no profit data is found, it returns “Profit not available”.

Example 5:

Purpose of example: Use DGET with SUM to add all the sales for a specific region.
Data tables and formulas:

	A	B	C	D
1	Region	Sales	Product	Formula
2	East	120	A	`=SUM(DGET($A$1:$C$4, "Sales", $E$1:$F$2))`
3	West	140	B
4	East	150	C

Where E1:F2 is the criteria range:

	E
1	Region
2	East

Result table:

	A	B	C	D
1	Region	Sales	Product	Total
2	East	120	A	270
3	West	140	B	#VALUE!
4	East	150	C	270

Explanation: The formula sums up all the sales for the East region.

Example 6:

Purpose of illustration: Use DGET with VLOOKUP to find the price of a product.
Data tables and formulas:

	A	B	C	D
1	Product	Price	Stock	Formula
2	A	12	10	`=VLOOKUP("A", A2:C4, DGET($A$1:$B$4, "Price", $E$1:$F$2), FALSE)`
3	B	14	15
4	C	16	20

Where E1:F2 is the criteria range:

	E
1	Product
2	A

Result table:

	A	B	C	D
1	Product	Price	Stock	VLookup
2	A	12	10	12
3	B	14	15	#VALUE!
4	C	16	20	#VALUE!

Explanation: The formula uses VLOOKUP to find the price of product A.

Example 7:

Purpose of illustration: Use DGET with MAX to find the maximum sales value.
Data tables and formulas:

	A	B	C	D
1	Region	Sales	Product	Formula
2	East	120	A	`=MAX(DGET($A$1:$C$4, "Sales", $E$1:$F$2))`
3	West	140	B
4	East	150	C

Where E1:F2 is the criteria range:

	E
1	Region
2	East

Result table:

	A	B	C	D
1	Region	Sales	Product	Max
2	East	120	A	150
3	West	140	B	#VALUE!
4	East	150	C	150

Explanation: The formula finds the maximum sales value for the East region.

Example 8:

Purpose of illustration: Use DGET with MIN to find the minimum sales value.
Data tables and formulas:

	A	B	C	D
1	Region	Sales	Product	Formula
2	East	120	A	`=MIN(DGET($A$1:$C$4, "Sales", $E$1:$F$2))`
3	West	140	B
4	East	150	C

Where E1:F2 is the criteria range:

	E
1	Region
2	East

Result table:

	A	B	C	D
1	Region	Sales	Product	Min
2	East	120	A	120
3	West	140	B	#VALUE!
4	East	150	C	120

Explanation: The formula finds the minimum sales value for the East region.

Example 9:

Purpose of illustration: Use DGET with AVERAGE to find the average sales value.
Data tables and formulas:

	A	B	C	D
1	Region	Sales	Product	Formula
2	East	120	A	`=AVERAGE(DGET($A$1:$C$4, "Sales", $E$1:$F$2))`
3	West	140	B
4	East	150	C

Where E1:F2 is the criteria range:

	E
1	Region
2	East

Result table:

	A	B	C	D
1	Region	Sales	Product	Avg
2	East	120	A	135
3	West	140	B	#VALUE!
4	East	150	C	135

Explanation: The formula finds the average sales value for the East region.

Example 10:

Purpose of example: Use DGET with COUNT to count the number of sales entries for a specific region.
Data tables and formulas:

	A	B	C	D
1	Region	Sales	Product	Formula
2	East	120	A	`=COUNT(DGET($A$1:$C$4, "Sales", $E$1:$F$2))`
3	West	140	B
4	East	150	C

Where E1:F2 is the criteria range:

	E
1	Region
2	East

Result table:

	A	B	C	D
1	Region	Sales	Product	Count
2	East	120	A	2
3	West	140	B	#VALUE!
4	East	150	C	2

Explanation: The formula counts the number of sales entries for the East region.

Part 3: Tips and tricks

Always ensure that your database range includes the column headers. Otherwise, the function might not work as expected.
The field argument must be enclosed in double quotation marks if it is a text.
If multiple records meet the criteria, DGET will return a #NUM! Error. To avoid this, ensure that only a single record matches your criteria.
Suppose the DGET function returns the #VALUE! Error, this means that no rows meet the criteria you have specified.
Consider using the Excel Advanced Filter feature and DGET if you have large datasets. It allows you to filter by multiple criteria and extract a list of records that meet complex conditions.
Always check your data for duplicates or similar records, which might cause the DGET function to return errors.

Post Views: 121

Leave a Comment