Exact Match of a text string(s) is possible in database functions like DSUM, DGET, DAVERAGE, DPRODUCT, DCOUNT, etc. in Google Sheets.

It is very important to know how to use an exact (accurate) match criterion in database functions aka D’ Functions in Google Sheets.

Actually it’s very simple! I’ll give you a few examples. Before going to the examples, you must know what impact an exact match can bring into the outputs of the database functions.

If there are two or more similar values matching the criterion in the database field (criteria field), the DGET function would return #NUM! error whereas most of the other database functions would return an inaccurate result.

Bit confusing, right? You will get a better idea about the same from my following database function examples.

Exact Match in DGET Database Function in Google Sheets

DGET doesn’t mean for aggregating values. It’s a lookup type (database) function.

Similar to the ‘index’ column in a Vlookup formula, the DGET returns the value of the ‘field’ specified in the second argument.

In other words, both ‘index’ (Vlookup) and ‘field’ (Database functions) refer to the same.

Syntax: DGET(database, field, criteria)
Syntax: VLOOKUP(search_key, range, index, [is_sorted])

If there are multiple matches or no match, the DGET formula would return #NUM! error. But the Vlookup will return #N/A error when there is no match and no issue with multiple matches.

Let’s leave that aside and concentrate on the exact match in database functions in Google Sheets. See the following DGET formula in cell F3.

=dget(A2:C8,3,E2:E3)

The formula returns the #NUM! error in cell F3 as the criterion “North” (E2:E3) has a ‘duplicate’ in the field ‘Area’. It’s the string ‘North East’.

To understand the term ‘field’ please scroll up and take a look at the syntax of the DGET in Sheets.

The criterion “North” and “North East” are actually not duplicates.

Just modify the criterion in cell E3 to “North East”. The formula will then return 1800 as the output. So the issue is the exact match, not duplicates.

We can avoid this error if we know how to do an exact match in the DGET database function in Google Sheets.

To perform an exact match in DGET database function, include the = sign with the criterion.

See the examples below.

Criteria as Cell Reference in DGET

Let’s use the same above DGET formula here. To exact match, we just need to use the criterion in cell E3 in a different way.

See the screenshot. I hope that makes sense.

Insert a single apostrophe and then an equal sign before the starting of the criterion. Without the apostrophe, Google Sheets will consider the entered criterion itself as some kind of formula.

Criteria (Hardcoded) within DGET

I like to use DGET in the above way. I mean the criterion from a cell reference. But if you want to use the exact match criterion within the DGET formula, use it as below.

=dget(A2:C8,3,{"Area";"=North"})

The equal sign makes the whole difference!

Exact Match in DSUM, DPRODUCT, DCOUNT, and DAVERAGE Database Functions in Google Sheets

Unlike DGET, you won’t find any formula errors related to exact match in database functions like DSUM, DPRODUCT, DCOUNT, DAVERAGE, etc. in Google Sheets. Because these functions aggregate values.

I am just using the function DSUM in my example for the exact match. It’s applicable to the other above database functions.

=DSUM(A2:C8,3,E2:E3)

The DSUM formula in cell F2 returns the total of the values 1000 in cell C3 and 1800 in cell C6. It’s the total of the ‘Qty’ in the ‘Area’ ‘North and ‘North East’.

To get the total only from the area ‘North’, as mentioned earlier, just change North in cell E3 to '=North.

When the DSUM criterion is within the formula, I mean hardcoded into the formula, the exact match will be as follows.

=DSUM(A2:C8,3,{"Area";"=North"})

Related Topics (Database Functions)

1. The Ultimate Guide to Using Criteria in Database Functions in Google Sheets.
2. How to Do a Case Sensitive DSUM in Google Sheets.
3. How to Use Date Difference As Criteria in DSUM in Google Sheets.
4. AND, OR in Multiple Criteria DSUM in Google Sheets (Within Formula).
5. Google Sheets: How to Use Multiple Sum Columns in the DSUM Function.