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)
=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.