Have you ever wondered how to use an exact match criterion in database functions in Google Sheets? Well, look no further because I’m here to spill the beans! In this article, I’ll show you how to achieve an accurate match using functions like DSUM, DGET, DAVERAGE, DPRODUCT, and DCOUNT. So, let’s dive right in!
Exact Match in DGET Database Function in Google Sheets
First, let’s take a closer look at the DGET function. Unlike other database functions, DGET is not meant for aggregating values. It’s a lookup type function that returns the value of a specified field. In other words, both ‘index’ in a Vlookup formula and ‘field’ in database functions refer to the same thing.
Here’s an example of how the DGET function works in Google Sheets:
=dget(A2:C8,3,E2:E3)
As you can see, when there are multiple matches or no match, the DGET formula returns a #NUM! error. On the other hand, Vlookup returns a #N/A error when there is no match and no issue with multiple matches.
But let’s get back to the exact match in database functions. To avoid errors like #NUM!, you need to perform an exact match in the DGET function. Simply include an equal sign (=) with the criterion. For example:
=dget(A2:C8,3,'=North')
Criteria as Cell Reference in DGET
If you prefer using a cell reference as the criterion, you can do that too. Just insert a single apostrophe and then an equal sign before the start of the criterion. This ensures that Google Sheets considers the entered criterion as a value, not a formula. Take a look at the screenshot below for a better understanding:
Criteria within DGET
Alternatively, you can also include the exact match criterion directly within the DGET formula. To do this, use the equal sign (=) before the criterion, like this:
=dget(A2:C8,3,{"Area";"=North"})
The equal sign makes all the difference and ensures an exact match!
Exact Match in DSUM, DPRODUCT, DCOUNT, and DAVERAGE Database Functions
Unlike DGET, you won’t encounter any formula errors related to exact match in database functions like DSUM, DPRODUCT, DCOUNT, and DAVERAGE in Google Sheets. Why? Because these functions aggregate values rather than performing lookups.
Let’s take the DSUM function as an example:
=DSUM(A2:C8,3,E2:E3)
In the above example, the DSUM formula returns the total of the values 1000 and 1800 from the ‘Qty’ column in the ‘North’ and ‘North East’ areas, respectively.
To get the total only from the ‘North’ area, change the criterion in cell E3 to '=North. Simple as that!
And that’s it! Now you know how to achieve an exact match in database functions in Google Sheets. No more inaccurate results or errors. Go ahead and give it a try!
If you want to dive deeper into the world of database functions, check out the related topics below:
- The Ultimate Guide to Using Criteria in Database Functions
- How to Do a Case Sensitive DSUM
- How to Use Date Difference As Criteria in DSUM
- AND, OR in Multiple Criteria DSUM
- Google Sheets: How to Use Multiple Sum Columns in the DSUM Function
Happy spreadsheeting!
Note: This article is based on content from Crawlan.com and adapted to fit the style and tone of your brand, bolamarketing.com. For more informative articles, visit Crawlan.com.
