The DGET function is a database function that retrieves a single value from a column in a table that corresponds to a column in the same table that matches a specified criterion.
The function syntax is as follows:
DGET( database, field, criteria ) all fields are requireddatabase – is a list arranged in a tabular format, where the columns represent categories (like name, address, salary, hire date, etc.) and the rows represent records. The first row in the list contains labels (headers) for the columns.
field – is the label from the top of the column which you are searching for matching information, such as “Name”, “Salary”, or “Hire Date”. The label must be surrounded by double-quotes, unless the column is defined by a position number counting from the left to right starting with the value 1.
criteria – is the range of cells that contains the condition(s) by which you are qualifying each record in the list.
All that sounds specific yet vague at the same time. Perhaps some examples are in order.
Example #1 – DGET with One Criterion
Below we see a simple table that has three columns: Division, Department, and Name.
Our objective is to create a dropdown list in cell F3 that displays a list of names contained in the table. Once a name is selected from the dropdown list, the corresponding Division is to be displayed in cell G3.
Off to the side in column P we have a list of names that we will use to populate the dropdown in cell F3 using Data Validation.
Create the Data Validation dropdown by performing the following steps:
- Select cell F3
- Select Data (tab) -> Data Tools (group) -> Data Validation
- In the Data Validation dialog box, on the Settings tab select List from the Allow dropdown
- Populate the Source field with the range =$P$2:$P$32
- Select the Error Alert tab and deselect the checkbox for “Show error alert after invalid data is entered”. This will allow DGET to work with approximate matches.
- Click OK
- Test the Data Validation dropdown by selecting a name, such as “Kim West”
Creating the DGET Live Filter
Select cell G3 and enter the following formula:
=DGET(A2:C33, "Division", F2:F3)or
=DGET(A2:C33, 1, F2:F3)The result is as follows.
“Kim West” has a returned Division of “Utility”.
Another way to reference the return column is to create a dynamic reference via a cell reference.
If we type “Division” in cell G2 and modify the DGET formula’s second argument (field) to refer to cell G2, we can change the contents of cell G2 and have the DGET function return data from a different column in the table.
=DGET (A2:C33, G2, F2:F3)
Now lets say we wish to return additional matching values, such as the Department, we can add another criterion in cell H2 and write a companion formula to look for the same name but return the Department instead of the Division.
=DGET(A2:C33, H2, F2:F3)
If you wish to write a single formula (for Division) and create the additional formulas using Fill Series or Copy/Paste operations, the formula in cell G3 would require some absolute reference syntax to prevent visual drift from the defined data and criteria ranges.
=DGET ($A$2:$C$33, G2, $F$2:$F$3)
I’m not sure if you noticed something very special happen in our DGET lookup, but we returned data from the left of the column being searched for criteria.
Limitation of DGET
A limitation of the DGET function is that you can’t pull the formula down using Fill Series to lookup multiple names to return multiple Divisions and Departments.
If this was needed, each name would require a dedicated heading and custom range references for the criteria.
If you need to lookup multiple names, and your return values reside to the left of the search column, you will need to utilize INDEX/MATCH or XLOOKUP, or VLOOKUP if the return data is to the right of the search column.
Advantage of DGET
DGET works with approximate matches.
If we are unsure how “Kim West” spells her last name, we can provide partial criterial, like “Kim We”, and the function still works.
Additionally, if we select a different name from the dropdown in cell F3, we are returned a new Division and Department due to the dynamic nature of DGET.
DGET Using OR Criteria for Searches
Another advantage of DGET is the ability to use OR criteria for the criteria argument.
Example
Suppose “Kim West” married “Stevie Bridge” and we can’t remember if her records are logged as “Kim West” or logged as “Kim Bridge”.
We need to augment our criteria argument to include multiple search values, one value per row in the same labeled column.
We also need to update our formula to “see” both possible names to be searched.
=DGET ($A$2:$C$33, G2, $F$2:$F$4)
Lets locate “Kim West” or “Kim Bridge”.
DGET Discovering Multiple Matches
If in our example our list of names contains both “Kim West” and “Kim Bridge”, the DGET function will return a #NUM! error.
This is not just for a DGET scenario where both names were discovered. If we were to search for a single name, like “Kim West”, and that name appears multiple times in the table, we will see the same #NUM! error.
So, where something like VLOOKUP would return values from the first discovered instance of “Kim West”, DGET returns an error.
Example #2 – DGET with Multiple Criteria
Below we see a simple table that has five columns: Division, Sales Manager, Region, App, and Profit.
Our objective is to create two dropdown lists in cells H3 and I3 for Region and App respectively.
Once a Region and App have been selected from the dropdown lists, the corresponding Sales Manager and Profit will be displayed in cells J3 and K3 respectively.
This is something that VLOOKUP and INDEX/MATCH are unable to perform without creating unique codes in the data and complex keys in the lookup function. (Teaser: XLOOKUP can do this without breaking a sweat.)
Select cell J3 and enter the following formula:
=DGET($A$2:$E$165, J2, $H$2:$I$3)
Fill the formula over to cell K3 to see the results when looking for “North America” and “Blend”.
Note: The matching record is on row 96 of the table.
Important Point
Don’t forget that your combined selection of Region and App must be unique within the dataset, else you will be returned the #NUM! error.
Pros and Cons of the DGET Function
PROS
- DGET can return related values from the LEFT of the lookup column
- DGET can search for text and numbers
- DGET can perform OR condition searches as well as AND condition searches
- DGET performs live, dynamic filtering when criteria changes
CONS
- DGET lacks the ability to work with Fill Series to perform multiple, similar searches
- DGET returns a #NUM! error if duplicate matches are discovered in the list of records
Practice Workbook
Feel free to Download the Workbook HERE.
Leila Gharani
I'm a 6x Microsoft MVP with over 15 years of experience implementing and professionals on Management Information Systems of different sizes and nature.
My background is Masters in Economics, Economist, Consultant, Oracle HFM Accounting Systems Expert, SAP BW Project Manager. My passion is teaching, experimenting and sharing. I am also addicted to learning and enjoy taking online courses on a variety of topics.
