How to Use DGET Function in Excel: 4 Methods

Method 1 – Using the DGET Function with One Criterion in Excel

Steps:

• Activate Cell C12.
• Type the following formula-

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

Or the column number relative to the database-

Apply it for another criterion then follow the steps-

•  Type the criteria beside the previous output.
• Drag right the Fill Handle icon to copy the formula.

Here’s the output for another criterion.

Method 2 – Applying the DGET Function with OR Criteria for Searches

Steps:

• Write the following formula in Cell C12–

• Hit the Enter button.

Then you will notice that the ‘Polo T-Shirt’ item is not in the list, so the DGET function has given the output for the matched item ‘T-Shirt’.

Method 3 – Using the DGET Function with Multiple Criteria in Excel

Steps:

• In Cell D13 type the following formula –

• Click the Enter button.

Then you will get the price for the blue shirt.

Method 4 – Using the DGET Function for Partial Matches in Excel

Steps:

• Write the following formula in Cell D13–

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

Error 1: DGET Function for Multiple Matches

If the lookup value is duplicated, 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-

If the lookup value is not in the database and it is partially matched 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 to create a new table with headers and apply the DGET function individually to return the expected result. I have created 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.

<< Go Back to Excel Functions | Learn Excel