Quick Links
DGET is a straightforward lookup function designed to retrieve a single value from a column in a table or database. It’s particularly useful for extracting a single point of data from a large spreadsheet, saving you from scrolling endlessly to find the info you need.
In this guide, I’ll walk you through the function’s syntax, show you some real-world examples, and discuss some of its pros and cons.
where
All three arguments for this function are required, meaning if you omit any, Excel will return a #VALUE! error.
So that I can explain this to you more clearly, here are some examples.
Example 1: One Criterion
Let’s start with this very basic example, which is a list of employees' IDs, names, departments, and lengths of service.
The Spreadsheet Setup
The blue table at the top is my retrieval table, and the green table underneath is my database. The aim is to return an employee’s first name, last name, department, and service length in the blue retrieval table when I input their ID into cell A2.
Before I show you how to pull data from the green database table to the blue retrieval table, let me highlight a few important things in the screenshot above:
Adding a Drop-Down List
To save me from having to type an employee’s ID into cell A2 each time, I willcreate a drop-down listof these numbers.
If you want to do the same, select the relevant cell, and click “Data Validation” in the Data tab. Then, choose “List” in the Allow field, and select the cells containing the drop-down data in the Source field. In my example, even though I only have 175 IDs in my database, I have extended the data validation list to cell A236, so that any further IDs I add will be included in my drop-down.
Notice how cell A2 now contains a drop-down arrow, which can be clicked to show the full list of IDs.
With one of these IDs selected, I’m now ready to begin my DGET retrieval.
In cell B2, I will type:
because cells A4 to E172 represent my database, the value in B1 (first name) is the category or field I want Excel to search for, and cells A1 and A2 (the category name “ID” and the ID in cell A2 selected from my drop-down) are the criteria. When I press Enter, I can see that Excel has successfully retrieved the first name based on the ID in cell A2.
Argumentsaandccontain dollar ($) symbols before the column and row references because they areabsolute references. In other words, these references will never change—I will always use the ID to create the lookup, and the database will always be in these cells. I added these dollar symbols by pressing F4 after adding each reference to my formula.
However, I deliberately left argumentbas a relative reference, since I will now use Excel’s fill handle to apply the same formula to the remaining categories in my retrieval table (last name, department, and service length).
Notice how the formula in cell E2 retrieves the field name from cell E1 as a result, while the database and criteria references have remained fixed.
I can now choose a different ID in cell A2 using the drop-down I created to retrieve the details of other employees.
If you have formatted your database usingExcel’s table formatting tool, argumentawill be the table’s name (also known asa structured reference) instead of its cell references.
Example 2: Multiple Criteria
To make the lookup more specific—useful if your DGET keeps returning the #NUM! error due to there being more than one match—you’re able to use more than one criterion in argumentc.
Here, I want to return the ID, first name, and last name of an employee who I know has worked in the Personnel department for ten years but whose name I can’t quite remember.
First, in cell A2, I will type:
where cells A4 to A172 contain my database, cell A1 is the category, and cells D1 to E2 contain my two criteria. In effect, Excel is creating an AND logical sequence between cells D2 and E2 to define my criteria.
Because I fixed my database and criteria references, but left the category reference relative, I can duplicate the formula in the remaining cells of my retrieval table to remind myself of this employee’s name.
If you’re more familiar withVLOOKUP, you might have noticed that you may use DGET to retrieve data from the right or left of where you are entering your formula, a flexibility that VLOOKUP does not offer.
you’re able to also create an OR logical sequence by adding another row to your retrieval table. For example, if I knew that someone had been employed for one or two years, but I couldn’t remember their name, I would type1in cell E2,2in cell E3, and extend argumentcto cover cells E1 to E3. Excel would then look for and return an entry that had either 1or2 as the service length. However, if more than one person fulfilled these criteria, Excel would return the #NUM! error.
The Pros of Using DGET
You may be wondering, “Why should I use DGET when there are other, more advanced functions?” Well, here are some benefits of using this tool:
The Cons of Using DGET
On the other hand, while DGET’s simplicity makes it easy to use, it also means that there are some drawbacks to be aware of:
DGET Drawbacks
How to Fix Them
You can only look up one record at a time. Each lookup requires its own heading and criteria.
Use XLOOKUP (or VLOOKUP if the return array is to the right of the lookup array), or create separate DGET retrieval areas for multiple searches.
If there are multiple matches, DGET returns a #NUM! error.
Amend the data so that there are no duplicates, or use VLOOKUP, which returns the data from the first matching value it finds.
DGET doesn’t work with horizontal tables (where the categories are in rows, and the data are in columns).
UseExcel’s transpose toolto flip the database’s structure, use HLOOKUP, which is designed to accommodate horizontal tables, or use XLOOKUP, which can search in any direction.
In this article, I’ve discussed DGET, VLOOKUP, HLOOKUP, and XLOOKUP, some of Excel’s best-known lookup functions. However, it would be remiss of me not to mentionINDEX and MATCH, which—when combined—are powerful, flexible, and adaptable alternatives.
