In this article, you will learn how to use the DGET formula in Google Sheets.
The DGET function is a database function that basically extracts a single value from a column of a database table or range of cells that meet specified criteria. You must prepare a well-organized dataset with column headers and a common rule for all database functions, such as DSUM, DCOUNT, and DCOUNTA.
The DGET formula can be helpful in Google Sheets when you need to extract a single value from a database or a range of cells that meet specific criteria. Here are a couple of cases where the DGET formula can be helpful:
You can insert the DGET formula in Google Sheets as follows:
The general syntax of the DGET formula is as follows:
database: This argument should be a range or array whose first row contains table headers (or field names), followed by values in rows (from the second row and downwards) for each field. The “database” could be something like the table in the picture below.
field: This parameter should be one of the column headers in “database”. The formula extracts a value from this column that meets specific condition(s). This argument can be a text string (manual input) of the table header or a column number of the column you want to refer to, assuming the leftmost column in the selected dataset is 1. You can use a cell reference as well.
criteria: This argument should be a range or an array whose first row contains field name(s), followed by specific condition(s) in the second row and downwards.
Note: While the other D-functions aggregate numbers (or the number of items) when you find more than one item that meets specific criteria, the DGET function is the only database function that doesn’t aggregate. Thus, the DGET formula returns an error value to you when it finds more than one match in the dataset. Also. it gives you an error value when it finds no match in the given data
Look at the following cases where we apply the DGET function to the sample sales list above. Imagine you want to know the specific sales amount that meets the criteria you have in mind.
In the first example, assume you need to get the sales from Client A in the list.
Although we highly recommend you use cell references in the DGET formula as they make the formula easy to follow and dynamic, if you are keen to use the DGET function with manual inputs for the “field” and “criteria” parameters, you can insert the formula as follow:
Bear in mind that you must enclose text strings with quotation marks and enter a table header and criterion separately, split by a semicolon and surrounded by a pair of curly brackets.
You can replace the table header provided by manual input or cell reference with a column number, 4 in this case, as “Sales Amount” is in the fourth column in the dataset.
With these formulas, you can get the returned value of 1000. The formula extracts 1000 from the first row as the row contains “Client A” in the “Client” column.
The second example shows when the DGET doesn’t return a proper value. This happens because, as discussed in the note above, the DGET function accepts only one match. As we highlight three rows in the picture, there are three matched if we only consider the “Type Z” product in the sales list. Thus, the formula can’t identify a match and gives you the “#NUM!# error value.
So, in the third example, consider adding one more criterion that “Contract Date” is “2/5/2023
“, in the DGET formula to extract one of the sales values. As you can see, the formula shows a specific number, 6,000, as only one item meets two requirements. The formulas with cell references and manuals look as follows, respectively:
Here are some major differences between the two functions:
In summary, the DGET function requires a well-organized dataset with column headings, but it is more flexible in terms of search method and the number of criteria, compared to the VLOOKUP formula.
Are you learning this function to visualize financial data, build a financial model, or conduct financial analysis? If so, LiveFlow may help you automate manual workflows and keep numbers updated in real-time. You can access more than fifty financial templates on our website, from the simple Income Statement to Multi-Currency Consolidated Financial Statement. You can also customize these templates as you want without breaking the automated data inflow.
To learn more about LiveFlow, book a demo.
You can learn about other Google Sheets formulas and tips that are not mentioned here on this page: LiveFlow‘s How to Guides
Learn how to do this step-by-step in the video below 👇