I Stopped Using XLOOKUP, VLOOKUP, and INDEX MATCH... Here's why, by Excel Dictionary on YouTube. 5 min 11 s.
- Video length
- 5 min 11 s
- Time to do it
- 10 minutes
- Difficulty
- Medium
- Steps
- 9
What you need
Steps
Put your data in a normal table with one header row at the top. DGET reads those headers to know which column you want back, so keep them short and unique.
Copy the exact header names of the columns you want to filter on into empty cells, side by side. Leave at least one empty row under them for the values you will search for.
Type your search value in the row under the header. For a simple lookup you need just one header and one value below it.
Write the formula as =DGET(database, field, criteria). Database is the whole table including headers, field is the column name in quotes or its number, criteria is the small range you built.
Press Enter to get the matching value. Change the value in the criteria cell and the result updates straight away, with no formula editing.
For a multi-condition lookup, add more headers to the criteria range and put a value under each one in the same row. DGET then matches all of them together.
For partial matches, type a wildcard in the criteria cell, like Ex* to find anything starting with Ex, or? to stand in for a single unknown character.
For OR logic, add a second row to the criteria range and put the alternative values there. DGET then returns a match from either row.
Watch for the two limits. If more than one record matches, DGET returns a #NUM! error, and if nothing matches it returns #VALUE!.
Tips and common mistakes
The criteria headers must match the table headers exactly, including spaces and capitals, or DGET will not find the field.
Keep a blank row under your criteria values so DGET does not read other nearby data as part of the range.
Use DGET when you need one answer back. If you want a list of matches, a filter or a spilling formula is easier.
Wildcards go in the criteria cells, so you never need to add them inside the formula itself.
Questions
Why does DGET return #NUM!?
More than one row in your table matches the criteria. Add another condition so only one record fits, and the error goes away.
Can DGET return text as well as numbers?
Yes. Point the field argument at any column and DGET returns whatever sits in that cell, text or number.
Do I need a special Excel version for DGET?
No. DGET is an old database function and works in every modern version of Excel, including Microsoft 365.
This guide was drafted from the video's details and general task knowledge. It has not been checked against the full video. Check the video and relevant product instructions before following a procedure. Report a correction.
