How To Use The DGET Function In Google Sheets

Reading Time: 5 minutes

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 columns
field – 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.

Example to understand how to use 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.
Add a criteria 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.
Fill in the criteria as shown
  • Select the empty cell where you wish to display the output
Select the empty cell as shown
  • 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.
Type in the formula as shown
  • Enter the range of the database as the first parameter. In this case, it will be A3:D9
Fill in the parameters as shown
  • 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
Fill in the parameters as shown
  • 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)
Fill in the parameters as shown
  • Press Enter and you will see the output same as the following image
Successfully implemented the DGET function in Google Sheets

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/

Leave a Reply