# 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-
`=DGET(\$B\$4:\$E\$9,C11,\$B\$11:\$B\$12)`
• 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)`

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
`=DGET(\$B\$4:\$E\$9,C11,\$B\$11:\$B\$13)`
• 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 –
`=DGET(B4:E10,D12,B12:C13)`
• 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
`=DGET(B4:E10,D12,B12:C13)`
• 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-

`=VLOOKUP(B13,B5:E10,3)`

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.

<< Go Back to Excel Functions | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!
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

Advanced Excel Exercises with Solutions PDF