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.

**Table of Contents**hide

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

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

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.

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

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.

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

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

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

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