DGET Function (Functions in Excel)

Product	Wattage	Life Hours	Brand	Unit Cost	Box Quantity	Boxes In Stock	Value Of Stock
Bulb	200	3000	Horizon	£4.50	4	3	£54.00
Neon	100	2000	Horizon	£2.00	15	2	£60.00
Spot	60						£0.00
Other	10	8000	Sunbeam	£0.80	25	6	£120.00
Bulb	80	1000	Horizon	£0.20	40	3	£24.00
Spot	100	unknown	Horizon	£1.25	10	4	£50.00
Spot	200	3000	Horizon	£2.50	15	1	£37.50
Other	25	unknown	Sunbeam	£0.50	10	3	£15.00
Bulb	200	3000	Sunbeam	£5.00	3	2	£30.00
Neon	100	2000	Sunbeam	£1.80	20	5	£180.00
Bulb	100	unknown	Sunbeam	£0.25	10	5	£12.50
Bulb	10	800	Horizon	£0.20	25	2	£10.00
Bulb	60	1000	Sunbeam	£0.15	25	1	£3.75
Bulb	80	1000	Sunbeam	£0.20	30	2	£12.00
Bulb	100	2000	Horizon	£0.80	10	5	£40.00
Bulb	40	1000	Horizon	£0.10	20	5	£10.00

How many boxes of a particular item do we have in stock?

Product	Wattage	Life Hours	Brand
Bulb	100		Horizon

The number in stock is :

5

=DGET(B3:I19,H3,C23:F24)

What Does It Do ?

This function examines a list of information and produces one result.

If more than one record matches the criteria the error #NUM is shown.

If no records match the criteria the error #VALUE is shown.

Syntax

=DGET(DatabaseRange,FieldName,CriteriaRange)

The DatabaseRange is the entire list of information you need to examine, including the

field names at the top of the columns.

The FieldName is the name, or cell, of the values to Get, such as "Value Of Stock" or I3.

The CriteriaRange is made up of two types of information.

The first set of information is the name, or names, of the Fields(s) to be used as the basis

for selecting the records, such as the category Brand or Wattage.

The second set of information is the actual record which needs to be selected, such

as Horizon as a brand name, or 100 as the wattage.

Formatting

No special formatting is needed.

Example 1

This example extracts information from just one record.

How many boxes of a particular item do we have in stock?

Product	Wattage	Life Hours	Brand
Bulb	100		Horizon

The number in stock is :

5

=DGET(B3:I19,H3,C51:F52)

Example 2

This example extracts information from multiple records and therefore shows the #NUM error.

How many boxes of a particular item do we have in stock?

Product		Wattage	Life Hours	Brand
Bulb		100
			The number in stock is :	#NUM!	=DGET(B3:I19,H3,C63:F64)
	Example 3
	This example extracts information from no records and therefore shows the #VALUE error.
	How many boxes of a particular item do we have in stock?

Product	Wattage	Life Hours	Brand
Bulb	9999
		The number in stock is :	#VALUE!	=DGET(B3:I19,H3,C64:F65)

Example 4

This example uses the =IF() function to display a message when an error occurs.

How many boxes of a particular item do we have in stock?

Product	Wattage	Life Hours	Brand
Bulb	9999
		The number in stock is :	#VALUE!	=DGET(B3:I19,H3,C85:F86)

No such product.

=IF(ISERR(F88),CHOOSE(ERROR.TYPE(F88)/3,"No such product.","Duplicates products found."),"One product found.")

Related Posts by Categories