How to Use DGET Function in Excel (4 Examples)

For looking up values we often use the VLOOKUP or XLOOKUP functions but there are some limitations like we can’t look up a value based on multiple criteria or look up a value in a column that resides to the left of the search column in the data’s table. We can overcome these situations by using the DGET function in Excel.

The above image is an overview of the DGET function. From this article, you’ll get the proper idea to use the DGET function with a vivid illustration.


Download Practice Workbook

You can download the free Excel template from here and practice on your own.


Introduction to the DGET Function

Purpose:

Excel DGET Function is used to return the single value in a given field from a range that matches the criteria you specify.

Syntax:

DGET(database, field, criteria)

Arguments:

Arguments Required/Optional Explanation
database Required It’s the database range including headers.
field Required The field name or index to count which you are searching for.
criteria Required  The criteria range including the headers.

Return Parameter:

Returns the single value in a given field from a range.


4 Examples of Using the DGET Function in Excel

Let’s get introduced to our dataset first. I have placed some cloth items and their corresponding sizes, colors, prices in my dataset.

How to Use DGET Function in Excel: Sample Dataset


Example 1: Use DGET Function with One Criterion in Excel

We can easily look up a value with a single criterion using the DGET function. Here, I’ll search for the price of the item ‘Shirt’.

Steps:

  • Activate Cell C12.
  • Then type the following formula-
=DGET($B$4:$E$9,D11,$B$11:$B$12)
  • Later, just press the Enter button and you will get the price from the table.

DGET Function with One Criterion in Excel

Instead of using the cell reference, you can use the criteria name in the formula-

=DGET($B$4:$E$9,”Price”,$B$11:$B$12)

Or the column number relative to the database-

=DGET($B$4:$E$9,4,$B$11:$B$12)

Now if you want to apply it for another criterion then follow the steps-

  •  Type the criteria beside the previous output.
  • Then just drag right the Fill Handle icon to copy the formula.

DGET Function with One Criterion in Excel

Here’s the output for another criterion.


Example 2: Apply DGET Function Using OR Criteria for Searches

Another advantage of the DGET function is that we can use OR criteria in it. Suppose, you can’t remember the product name but you are sure that it will be a T-Shirt or Polo T-Shirt then you can search for both at a time.

Steps:

  • Write the following formula in Cell C12
=DGET($B$4:$E$9,C11,$B$11:$B$13)
  • Finally, just hit the Enter button.

Excel DGET Function Using OR Criteria for Searches

Then you will notice that as there is no ‘Polo T-Shirt’ item in the list that’s why the DGET function has given the output for the matched item ‘T-Shirt’.


Example 3: Use DGET Function with Multiple Criteria

For this example, I have modified the dataset a little bit. Take a look that now there are two items with the same name ‘Shirt’ but the other criteria are different. We’ll search here for the blue color between the two ‘Shirt’ items.

Steps:

  • In Cell D13 type the following formula –
=DGET(B4:E10,D12,B12:C13)
  • Next, click the Enter button.

Then you will get the price for the blue shirt.

DGET Function with Multiple Criteria in Excel


Example 4: Use DGET Function for Partial Matches in Excel

The DGET function allows partial matches too. If we partially write any of the criteria then we’ll get the result too. I have written ‘Blu’ for the partial match in color criteria.

Steps:

  • Write the following formula in Cell D13
=DGET(B4:E10,D12,B12:C13)
  • Then after pressing the Enter button you will get the result.

DGET Function for Partial Matches in Excel


Errors with DGET Function

While using the DGET function you will face #NUM! error and #VALUE! error in some cases. Now I’ll show the errors and their solutions.

Error 1: DGET Function for Multiple Matches

If there is any duplicate of the lookup value then you will get the #NUM! Error. See that there are two shirt items.

Excel DGET Function for Multiple Matches

Solution:

Its solution is using the VLOOKUP function. VLOOKUP will give the output for the last match.

Use this formula for that-

=VLOOKUP(B13,B5:E10,3)

Excel DGET Function for Multiple Matches

If the lookup value is not in the database and it is partially matched then it will show the #VALUE! error like the image below.

Solution:

Its solution is like the solution of the #NUM! error.


Error 2: DGET Function for Multiple Rows

The DGET function doesn’t allow the use of the down Fill Handle tool. If we do it then it will show #VALUE! Error because the criteria reference gets changed.

Excel DGET Function for Multiple Rows

If we keep the header row frozen then it will show the #NUM! error.

Excel DGET Function for Multiple Rows

Solution:

The solution to both errors is just to make a new table with headers and apply the DGET function individually to return the expected result. Look that I have made a new table with headers for Jeans.


Advantages and Disadvantages

Advantages:

  • The DGET function will return related values from the LEFT of the lookup column.
  • It can search only for text and numbers.
  • It can work for OR condition searches as well as for AND condition searches.
  • The DGET function can perform live, dynamic filtering when criteria change.

Disadvantages:

  • The DGET function cannot work with Fill Series to perform multiple, similar searches.
  • It will return #NUM! error if duplicate matches are discovered in the database.

Conclusion

I hope all of the methods described above will be good enough to use the DGET function in Excel. Feel free to ask any question in the comment section and please give me feedback.

Mithun

Hello! I am Md. Sourov Hossain Mithun. Welcome to my profile. Excel is an amazing softwear. Here i will post excel related useful articles. I am a graduate from Bangladesh University of Engineering and Technology. I love to learn new things and work with it. Thank you.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo