Excel DGET Function – How to use guide with Examples

Excel DGET function is one of the database functions that return a single value that matches a specific condition or even multiple conditions from a column of the table or database. Put simply, DGET is a lookup function.

The purpose of a lookup function is simple. It extracts a value that matches a set of criteria from the selected data.

VLOOKUP and INDEX/MATCH are goto functions to perform lookup tasks in Excel.

However, if your lookup range has duplicates or you want a single value that matches more than one condition, VLOOKUP and INDEX/MATCH won’t cut it easily. Like the following example:

If you are asked to find the sales agent that had less than 10 attendance, VLOOKUP can’t do it as the return column is to the left of the lookup column.

Or if you are required to search for sales value based on agent name, VLOOKUP and INDEX/MATCH won’t help as agent names are repeated.

Again, if you are asked to find which store got more than 500 sales in February. As we are asking for multiple conditions, VLOOKUP and INDEX/MATCH require complex workarounds even if we try.

This is where DGET function enters the lookup arena. And ace all of the above issues with ease!

Excel DGET Syntax

=DGET(database, field, criteria)

• database = a data range that contains headers for each column. Arranging data in such a format is called tabular format. For DGET function to work, this is a must. As DGET will use the column headers to identify each column and to apply criteria.
  For the same reason, we will include the headers of the data as well while selecting the data.
• field = column header is simply called field in database terms. If columns are specified with names, they must go in double quote.
  We can also use numbers to specify certain column just like we do in VLOOKUP.
• criteria = data range with criteria including column headers. You can specify a condition or multiple conditions for a single field under one column. Or multiple conditions for multiple fields.

DGET Example 1 – Single criterion for a field

Following is a simple example

Say we want to know the name of the agent working for store S7. A simple lookup to the left.

But before we start writing the formula, we need to put criteria in a table format as follows:

Notice the criteria in green color with heading and store name in A10:A11 range. Now we can proceed with finding the agent for store S7 with the following formula:

=DGET(database,field,criteria)

=DGET(A1:C8,"Agent",A10:A11)

And the result is = Loot

DGET Example 2 – Single logical criterion for a field

Taking the same example, this time we like to find a sales agent with a sales volume greater than 850.

Following is the criteria for this requirement:

Instead of “Store” we now have “Sales” and “>850” is a logical test we placed. The formula remains the same as we are still looking for the agent name but here it is again:

=DGET(A1:C8,"Agent",A10:A11)

DGET Example 2 – Multiple criteria for a field

With DGET you can have multiple conditions to find a single matching value. To do that we simply need to add a row to a certain field.

Say we would like to know the employee that made sales equal to OR greater than 850. It seems one condition but in actual they are two; equal to 850 is one condition, greater than 850 is the second.

For several other functions in Excel, we mention this situation with “>=” notation. However, DGET does not take two logical operators together. But we can mention the second condition in the second row under the criteria field as follows:

Remember 850 mentioned in A11 means ” equal to 850″ or simply “=850”. For A12 it means “greater than 850” or simply “>850”.

The following formula will get us the results:

=DGET(A1:C8,"Agent",A10:A12)

Arranging the criteria in a little descriptive form it looks like this:

[Sales = 850] 
OR 
[Sales > 850]

If put in words, it can be stated as:

Find the agent that made sales equal to 850 OR greater than 850

DGET Example 3 – Single criterion for multiple fields

Expanding our example with new variables, we can apply a certain condition on more than one field.

Say we like to know the employee that has made sales more than 300 AND attendance of more than 20 days in a month.

For this we will change the criteria as follows:

Put the following formula in cell B15 to know the answer:

=DGET(A1:F8,"Agent",A11:B12)

And the answer is Esa. As the data is small, we can even visually confirm the answer:

Arranging the criteria in a little descriptive form it looks like this:

[Sales > 300] AND [Attendance > 20]

If put in words, it can be stated as:

Find an agent that made the sales greater than 300 AND has an attendance greater than 20 days in a month.

DGET Example 4 – Multiple criteria for multiple fields

As seen in example 2 above, where we gave multiple conditions for a single field, we can provide multiple conditions for multiple fields.

Although from the perspective of a field, we can say we provide multiple conditions, in actual we are making additional OR queries. Let’s understand this with an example.

Have a look at the following criteria:

The following formula will get us the results:

=DGET(A1:F8,"ID",A11:C14)

And the result is 7767 as we can below:

Looking at the criteria again, we desired to find the transaction ID that matches the following criteria:

[seller Agent = Musa] AND [made Sales > 300] AND [had Attendance > 20 for the month]
OR
[seller Agent = Esa] AND [made Sales > 300] AND [had Attendance > 20 for the month]
OR
[seller Agent = Ishaq] AND [made Sales > 300] AND [had Attendance > 20 for the month]

If the above criteria are put in words, it’s like this:

Find the transaction ID where the transaction is greater than 300 AND the employee has an attendance of more than 20 days in a month and employees are EITHER Musa, Esa OR Ishaq.

Limitations of Excel DGET

With all the fun DGET gives, there are serious limitations as well. And probably the reaosn why DGET isn’t that popular as other Excel lookup functions.

#NUM! Error if multiple matching records

Unlike other lookup functions that return the first matching value from the dataset, DGET will throw an error if more than one matching record is found in the data for the specific criteria.

In the following illustration if we try to find a sales agent that made more than 500 sales, DGET will through an error as multiple matches are found:

For this situation VLOOKUP is better suited as instead of throwing an error it returns the first matching value from the data even if there are multiple matches.

Impossible to use on multiple searches

In Excel, if you intend to apply the same formula on multiple values down the column, you simply drag the fill handle down to fll the series or double click to populate the column down a certain cell.

With DGET however, it is next to impossible as for every query DGET requires the user to select the criteria with the field heading. In short, you have to make the criteria for every query separately.

In the following example, we would like to know the stores each agent is working for.

An extremely simple task for any lookup function. But not for DGET.

DGET requires the criteria to be provided including headers. We can do that for the first name without issues using this formula:

=DGET($A$1:$C$8,"Store",$A$11:A12)

Drag the fill handle down to populate the cells.

As you can see this has solved the first name correctly but for the rest, it’s giving an error.

To solve this we need to reformat our criteria in such a way that for each agent we provide a heading as follows:

This is how the formula looks for each name:

Impractical to say the least, as regular VLOOKUP and INDEX/MATCH can get this done without this much hassle.

Excel DGET Function – Summing it up!