The DGET function in Google Sheets is useful if you need a single value from a database table-like array or range using a SQL-like query.
DGET function is a built-in function that is categorized as a ‘Database Function’. It can be used as a worksheet function, and can be entered as part of a formula in a cell of a worksheet. It is the only database function in Google Sheets that can be used for vertical lookup. All database functions in Google Sheets are for aggregation except the
DGET. You can use the function to obtain values from multiple columns, not rows.
Let’s take an example.
I want to buy a pre-owned car and have collected a list of available models and details from the dealer in a tabular form. I want to know the year of manufacture for the Ford F150 variant. The list is really huge, and it is cumbersome to eyeball it line by line.
Here is where
DGET comes to my rescue. The function outputs the year of make for the F150 once I give the appropriate condition of the brand name.
That’s just one small example. There are plenty of other use-cases for this function in real life.
Before we move to the structure of the formula and examples, I would like you to note a couple of things. If you want to use
DGET to get data, you should make sure that your data complies with the following basic requirements:
- The data should be in tabular form with proper headers.
- The data should not contain any duplicates. If there are duplicates, you should remove them by using the
SORTfunction that you can use within the
DGET. If the function encounters multiple matches for the given criteria, it will return an error.
For a comprehensive guide on how to use the
SORT functions, refer to our articles here.
Great! Let’s dive right into real-business use-cases, where we will deal with actual values and as well as learn how we can write our own
DGET function in Google Sheets to calculate variances in data.
The Anatomy of the DGET Function
So the syntax (the way we write) of the
DGET function is as follows:
=DGET(database, field, criteria)
Let’s dissect this thing and understand what each of the terms means:
=the equal sign is just how we start any function in Google Sheets.
DGET()is our DGET function. DGET will return the variance of an entire population selected from a database table-like array or range using a SQL-like query.
- database refers to the array or range having the data, including headers for each column’s values
- field refers to the column in the data which has the values that are to be extracted and worked on.
- field may either be a text label referring to the required column header or a numeric value indicating which column to consider, where the first column has a value = 1.
- criteria refers to an array or range containing the criteria to filter the database values before operating. This may be left blank.
For a better understanding of the difference between estimating variance from a population vs. a sample, we shall be explaining the function using the same examples from our guide on
DGET function, so that you can compare the two.
A Real Example of Using DGET Function
Take a look at the example below to see how
DGET functions are used in Google Sheets.
The above figures are showroom prices for different brands of cars. The objective here is to find the price for the Dodge Viper. Just below the captured data, I have given a provision to enter the criteria based on which the data will be filtered. This is not a required criterion, and the function will still function properly if we leave it blank. As you can see below, I have obtained the showroom price for the Dodge Viper:
You may try changing the criteria and see how the result changes. Go ahead and make a copy of the spreadsheet using the link I have attached below:
Awesome! Let’s begin our
DGET function in Google Sheets.
How to Use DGET Function in Google Sheets
- Let’s see how to write your own
DGETfunction, step-by-step. I have listed down the top goal scorers from the past six FIFA World Cup tournaments. The objective is to identify which Spanish player was among the top scorers of the 2010 edition of the tournament.
- Now, simply click on any cell to make it the active cell. For this guide, I will be selecting A18, where I want to show my results.
- Next, simply type the equal sign ‘=‘ to begin the function and then followed by the name of the function, which is our ‘dget‘ (or DGET, whichever works).
- You should find that the auto-suggest box appears with our function of interest. Continue by entering the first opening bracket ‘(‘. If you get a huge box with text in it, simply hit the arrow in the top-right hand corner of the box to minimize it. You should now see it as follows:
- Now, the fun begins! Let’s give the required inputs to the function to get the standard deviation in unit sales, per the filtration criteria we have given above the data:
- Take note of how I’ve specified conditions to limit the data to the 2010 edition of the tournament. The criteria for the formula are input as A13:C14 to account for all the criteria mentioned, if any.
- Once you’ve entered the necessary database, field, and criterion values, or you’ve done what I did, make sure to close the brackets ‘)’, as shown below.
- Finally, just hit your Enter key. You will notice that we have a #NUM! output. The #NUM! error occurs in when a calculation can’t be performed. So what happened here?.
Error in DGET Output
If you look closely, there are four players who were joint top scorers for the 2010 edition, therefore the condition of 2010 does not return a unique result. It is very important to note this. You now have to give additional criteria to objectively get the result we desire. Go ahead and add another criteria before trying again. Once you’ve added another criteria, the result should automatically be updated as shown below:
You can now see that we have identified David Villa as the Spanish top scorer in the FIFA World Cup 2010 tournament. That’s pretty much it. You have everything you need to get started with the
DGET function on Google Sheets. I recommend experimenting with the
DGET function, combining it with the numerous Google Sheets formulas available, and seeing what you can come up with. 🙂