How to Use DGET Function in Excel (4 Examples)

Get FREE Advanced Excel Exercises with Solutions!

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.


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.


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

How to Use DGET Function in Excel: Sample Dataset


1. Using the 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,C11,$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.


2. Applying the DGET Function with 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’.

Read More: Excel DGET Function with Dynamic Criteria


3. Using the DGET Function with Multiple Criteria in Excel

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

Read More: Can Excel DGET Return Multiple Records


4. Using the 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 Excel 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.


Excel DGET Function: Pros and Cons

Pros:

  • 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.

Cons:

  • 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.

Download Practice Workbook

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


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.


<< Go Back to Excel Functions | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Md. Sourov Hossain Mithun
Md. Sourov Hossain Mithun

Md. Sourov Hossain Mithun, an Excel and VBA Content Developer at Softeko's ExcelDemy project, joined in October 2021. Holding a Naval Architecture & Marine Engineering degree from BUET, Mithun showcases expertise during his two-year tenure. With over 200 published articles on Excel topics, he earned a promotion to Team Leader, excelling in leading diverse teams. Mithun's passion extends to Advanced Excel, Excel VBA, Data Analysis, and Python programming, contributing significantly to the innovative and dynamic environment of ExcelDemy... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo