Free DGET Practice Workbook
Both examples from the video in one file: a left lookup with one criterion and a lookup with multiple criteria. Test the OR condition and see what happens with duplicates.
How to Use the DGET Function in Excel
Need to pull a specific value from a table based on certain conditions? Excel’s DGET function does exactly that.
Here’s what it does in plain English:
DGET looks through a table, finds one row that matches your condition, and gives you the value from the column you ask for.
DGET Function Syntax
DGET( database, field, criteria )All three parts are required:
- database: This is your full table, including headers in the first row.
- field: This is the column you want a value from. You can type the column name in quotes (like “Salary”) or use its number from left to right (1, 2, 3…).
- criteria: This is a small range where you set the condition. It must include the same header as in your table.
Example 1: DGET Lookup 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 lets you type part of a name. DGET still finds it, as you’ll see below.
- Click OK
- Test the Data Validation dropdown by selecting a name, such as “Kim West”
Write the DGET Formula
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 works in the Utility division.
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)
Return More Columns with One Formula
Want the Department too? Type Department in cell H2. Then write a second formula that uses H2 as the field.
=DGET(A2:C33, H2, F2:F3)

To write one formula and drag it to the right, lock the database and criteria ranges with F4. Leave the field reference unlocked so it moves from G2 to H2.
=DGET($A$2:$C$33, G2, $F$2:$F$3)
DGET Can Look Up to the Left
Notice what just happened.
DGET searched the Name column and returned the Division from a column on its left. VLOOKUP can’t do that.

Why You Can’t Copy DGET Down a List
DGET has one big limitation. You can’t copy the formula down to look up a list of names. You get #VALUE! errors instead.

To make it work, each name needs its own criteria range with a header. Each formula then points to its own range.

If you need to look up a whole list of names, use INDEX/MATCH or XLOOKUP. VLOOKUP also works when the column you want sits to the right of the search column.
DGET Finds Partial Matches
DGET doesn’t need the full text. A text criterion matches any value that begins with what you type, so Kim We still finds Kim West. Wildcards like * and ? work too.
Be careful with short entries, though. If two names start the same way, DGET finds both and returns #NUM!.
Need an exact match instead? Type this in the criteria cell:
="=Kim West"
Pick a different name from the drop-down and the Division and Department update right away.
DGET with OR Criteria
Another advantage of DGET is the ability to use OR criteria for the criteria argument.
Search for Two Possible Names
Suppose “Kim West” married “Stevie Bridge” and we can’t remember if her records are logged as “Kim West” or logged as “Kim Bridge”.
Add the second name in the cell below the first, under the same Name header. Each extra row is an OR condition.

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)

Now DGET finds Kim West or Kim Bridge, whichever one is in the table.
Why DGET Returns the #NUM! Error
If the table contains both Kim West and Kim Bridge, DGET returns a #NUM! error.

The same happens with one name. If Kim West appears twice in the table, DGET returns #NUM! too.

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.

VLOOKUP and INDEX/MATCH can’t do this easily. You’d need a helper column that joins both values, or an array formula like the ones in INDEX MATCH with multiple criteria.
In Microsoft 365 and Excel 2021 or later, XLOOKUP and FILTER can do it too. Here’s how to use the FILTER function with multiple criteria.
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.

Keep the Combination Unique
Your combination of Region and App must be unique in the data. Otherwise DGET returns #NUM!.
When to Use XLOOKUP or FILTER Instead
DGET is a good fit for older Excel versions. But if you have Microsoft 365, Excel 2021 or later, or Excel for the web, two newer functions do the same job with fewer rules. They don’t need a criteria range with headers, and you can copy them down a list.
XLOOKUP for a Single Result
XLOOKUP can look to the left too. Here’s Example 1 without a criteria range:
=XLOOKUP(F3, $C$3:$C$33, $A$3:$A$33)It searches the Name column and returns the Division. Put it next to a list of names, copy it down, and each row gets its own result. That’s the one thing DGET can’t do.
For two criteria, multiply the conditions. Each condition gives TRUE or FALSE for every row. Only the rows where both are TRUE give you a 1, and XLOOKUP looks for that 1:
=XLOOKUP(1, (C3:C165=H3)*(D3:D165=I3), B3:B165)This returns the Sales Manager for the Region in H3 and the App in I3. Change B3:B165 to E3:E165 to get the Profit.
There’s one difference to know about. When there are duplicates, XLOOKUP returns the first match, while DGET returns #NUM!. That’s handy, but it also means a duplicate can slip through without you noticing.
FILTER When You Want Every Match
FILTER goes one step further. If the combination isn’t unique, it returns every matching row instead of an error.
=FILTER(B3:B165, (C3:C165=H3)*(D3:D165=I3), "No match")The results spill into the cells below. The last argument shows No match when nothing is found. Leave it out and FILTER returns #CALC! instead.
When DGET Still Makes Sense
Stick with DGET if you, or the people you share the file with, use Excel 2019 or older. XLOOKUP and FILTER aren’t available there, so those formulas won’t work.
DGET is also a nice choice for a small input area, where a couple of drop-downs drive one result. You change the criteria and the answer updates instantly.
Pros and Cons of the DGET Function
Pros
- DGET can return values from a column to the left of the search column
- It works with text and numbers
- It handles AND and OR conditions
- It updates instantly when the criteria change
- It works in older Excel versions
Cons
- One formula handles one lookup, so you can’t copy it down a list
- It returns #NUM! when more than one record matches, not the first match
- Text criteria match the start of the text, so a short entry can hit several records
Download the DGET Practice File
Get the DGET Practice Workbook
Try every formula from this guide on the same data used in the video.
Frequently Asked Questions
What does the DGET function do in Excel?
DGET pulls a single value from a table that matches the conditions you set. You give it the table with headers, the column you want back, and a criteria range with a header and the value to match.
Can DGET look up a value to the left?
Yes. DGET does not care where the return column sits. It can search the Name column and return a value from a column on its left, which VLOOKUP cannot do.
How do I use DGET with multiple criteria?
Put the headers side by side in the criteria range, for example Region and App, and type the values in the row below. Values on the same row are AND conditions. Values on separate rows are OR conditions.
Why does DGET return #NUM! or #VALUE!?
#NUM! means more than one record matches your criteria, for example a duplicate name. #VALUE! means no record matches. Also check that the criteria header is spelled exactly like the header in your table.
Can I copy a DGET formula down a column?
Not for a list of different lookup values. Each DGET formula needs its own criteria range with a header. To look up a whole list, use XLOOKUP, INDEX and MATCH, or VLOOKUP instead.
Is DGET better than XLOOKUP?
Not usually. XLOOKUP and FILTER handle left lookups and multiple criteria without a criteria range, and you can copy them down. They need Microsoft 365, Excel 2021 or later, or Excel for the web. DGET is the better choice when you or your colleagues use Excel 2019 or older.
Leila Gharani
Founder of XelPlus and ten-time Microsoft MVP. Leila helps over 500,000 professionals master Excel, Power BI, and data automation through practical, real-world training.







