What it does – It’s the only function that performs vertical searches in Google Sheets
Syntax:=DGET(database, field, criteria)database – It refers to the range of data inclusive of the headers of the columnsfield – It refers to the columns in the database that are to be returned. The index of the columns is passed in this parameter and it follows one-based indexing criteria – It refers to the conditions/specifications which we are using to filter the database to get the desired output
Sample usage:=DGET(A3:D9, 4, A13:C14)
// This will return the 4th column which satisfies the criteria of vertical searches
Sample Google Sheets template with formula here.
There can be certain instances when one needs to perform vertical searches in a database. In such cases, the DGET function is the only database function in Google Sheets that can perform vertical searches. It is possible to use the DGET function in Google Sheets to perform the vertical lookup, but not to return values from rows but from columns. When you need to get a single value from an array or range of values in a database table, the DGET function in Google Sheets can help.
A few things to note before using the DGET function
Before applying the DGET function in Google Sheets, there are a few things you need to know. They are as follows:
- The data presented should be in tabular form along with the suitable headers
- There must not be duplicates in the table as we use the DGET function to return a single unique value. If your table contains duplicates then make sure to use the Unique or Sort function within the DGET function to remove duplicates. In case of multiple matches, the DGET function will return an error
- There must not be merged cells in the given data. It must be organized and well structured
Let’s say, we have a database of smartphones of different brands and models. We want to look for a particular model with a specific specification. In that scenario, we can use the DGET function for our convenience as we won’t need to go through the whole database.
In this tutorial, we will learn how to use the DGET function in Google Sheets. Let’s get started.
Using the DGET function in Google Sheets
In the following example, we have a database containing information about a range of products with unique specifications. The given database is in a structured form and does not contain duplicate values as that is the pre-requisite for applying the DGET function in Google Sheets.
The steps are as follows:
- Let’s say we want the price of the iPhone 14 model which is white in colour and has China as the country of origin. In this case, we will have to make criteria according to our requirements. We will use this criterion to filter out the database and give us the relevant output.
- The step to building criteria is quite simple. Copy the headers of the database and paste them into a separate region as shown.
- Now, below the headers fill in your requirements. In this case, we will fill iPhone 14 under the product header, white under the colour header, and China under the origin header.
- Select the empty cell where you wish to display the output
- Begin your function with the ‘=’ sign. Type in the ‘DGET’. The Google Sheets will prompt this function, press the Tab/Enter key to autocomplete. The tooltip guide will appear along with the details.
- Enter the range of the database as the first parameter. In this case, it will be A3:D9
- Enter the column index of the output we expect as the second parameter. In this case, we expect price as our output so fill in 4 as the index value
- Enter the range of criteria as our third parameter. In this case, it will be A13:C14. The complete formula should look as follows:
=DGET(A3:D9, 4, A13:C14)
- Press Enter and you will see the output same as the following image
We have successfully implemented the DGET function in Google Sheets.
Conclusion
The DGET function is the only inbuilt function in Google Sheets that can be used for vertical lookups. It can save a lot of time and effort as one doesn’t need to go through the whole database. We have successfully implemented the DGET function and learned everything necessary to get started.
Commonly asked questions
How does DGET function work?
The DGET function gets a single value from a given field in a record that matches the criteria. The database argument is a range of cells that includes field headers, a field is the name or index of the field to get a max value from, and criteria is a range of cells with headers that match those in the database.
What happens when the DGET function is not able to find values matching the criteria?
The DGET function returns #VALUE! error if the criterion doesn’t match. Therefore, one should wrap the DGET function with an Iferror. This way even if there is no value found, the DGET function won’t throw up an error. You can read more about errors here.
What happens if we use a database with duplicate values?
Duplicates in the criteria/lookup column are not allowed. This causes #NUM! error in the DGET function.
See Also
You have learned how to use the DGET function in Google Sheets. Are you interested in learning more about how much you can succeed with Google Sheets? With so many powerful features of Google Sheets, you save time and effort.
We have several tutorials that cover tricks and tips in Google Sheets. You can discover them here.
Here are some articles you might be interested in:
https://blog.tryamigo.com/how-to-use-sort-function-in-google-sheets/
https://blog.tryamigo.com/sort-query-using-order-by-in-google-sheets/
https://blog.tryamigo.com/introduction-to-date-function-in-google-sheets/