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.
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.
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.
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.
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.
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.
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.
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)
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.
If we keep the header row frozen then it will show the #NUM! error.
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