I’ve been overcomplicating Excel lookups with VLOOKUP — this obscure function changed that

For many people who use Excel, the instinctive response to any data-retrieval task is to do a VLOOKUP in their spreadsheet. It's so ingrained that using it feels almost like riding a bike. You don't really think about it; you just do it.

However, after struggling to get VLOOKUP to perform a multi-criteria lookup one day, I had to look for another option. That's when I stumbled upon DGET. It's not exactly a VLOOKUP killer, but it's a much smoother solution for lookups that require multiple conditions.

Why DGET beats VLOOKUP at multi-criteria lookups

No helper columns, no concatenated keys, just readable formulas

VLOOKUP wasn’t designed to juggle more than one condition at a time. It expects a single lookup value, searches for it in the first column of your range, and returns a result. That's fine when you're matching an employee ID or a product code. However, when you need to find something based on material, type, and size all at once, VLOOKUP becomes more stressful than it is helpful. To make it work, you'd need to build a helper column that mashes those three fields together with an ampersand before searching for the combined string instead of your actual data. It works, but it's ugly, and it gets even uglier every time someone inserts a column.

The DGET function sidesteps that whole mess. Instead of relying on static column numbers (like "6"), it uses your actual headers, such as "Cost," "Profit," or whatever you've labeled your columns, so the formula reads almost like plain language. If you want AND logic across three criteria, you simply place them in the same row of a small criteria table. If you need OR logic instead, place them in separate rows. You don't need helper columns, you don't have to alter your original data, and you don't have to build overly complex formulas.

For example, instead of creating a concatenated key and searching for "AluminumBoltM15," you'd set up a criteria range with Material, Type, and Size as the headers, place "Aluminum," "Bolt," and "M15" underneath them, and write a formula like this:

=DGET(A1:D100, "Price", F1:H2)

DGET can also return records that satisfy numeric conditions. For example, you can find an employee whose age is greater than 40 but less than 45. Anyone auditing your spreadsheet months later can glance at the criteria range and immediately understand what the formula is doing, which is more than I can say for all the concatenated lookup keys I used to build just to make VLOOKUP work. Plus, because DGET relies on column headers rather than column positions, it's less likely to break if you insert new columns into your data table, provided the criteria headers still match the database headers.

The basic structure of a DGET formula is this:

=DGET(database, field, criteria)

The database argument is the entire range of your data, including the top row of column labels. The field argument is the specific column you want to return data from. You can enter the column name in quotes (for example, "Age") or use its numerical position in the table (such as 3 for the third column). The final argument, criteria, is a separate range on your worksheet that contains your search conditions. This range must include at least one column header and at least one row beneath it for the criteria.

Before using DGET, you'll need to create a criteria range. If you want to find a record that matches multiple conditions (for example, where Item Type is "Cosmetics" and Order Priority is "H"), place both criteria in the same row under their respective headers. If you want to return a record that matches one condition or another (for example, where Item Type is "Cosmetics" or "Groceries"), place each criterion in a separate row under the same header. It's also worth keeping the criteria range away from the bottom of your main data table so you don't block yourself from adding new records later.

Once you've set up the criteria range, simply enter your DGET formula in the cell where you want the result to appear.

When DGET is the right lookup function

It’s brilliant at one job, so it isn’t always the answer

The DGET function comes with one condition: it expects exactly one matching record. If you ask it a question with no possible answers, it'll return a #VALUE! error, which is one of the most common errors in Excel. Ask for something with two matches, and you'll get a #NUM! error instead. As a result, it’s excellent for precise lookups, but terrible if your data contains duplicates or multiple valid matches.

There's also the criteria range to consider. Your search conditions need to exist somewhere on the worksheet rather than being written directly into the formula, as you might with other functions. If your database headers contain merged cells, DGET won't work properly either.

It's also worth acknowledging DGET's age. Some spreadsheet veterans consider it a relic of the 1980s, and in some ways, that’s not a complete exaggeration. It doesn't spill results dynamically the way modern array functions do, which matters if you're working with modern Excel. That's where XLOOKUP earns its reputation as the more versatile, direction-agnostic option for everyday lookups, while INDEX/MATCH continues to hold its ground thanks to its flexibility and backward compatibility.

I reach for DGET when I'm working with a fixed database structure, need to satisfy three or more conditions at once, and know the result should be a single unique value. However, I'd skip it when multiple matches are possible, when I need Excel's dynamic array functions, or when XLOOKUP and FILTER offer a more natural solution for the task at hand.

Not a VLOOKUP replacement, but a trick you'll probably keep using

DGET is a specialized tool rather than a general-purpose replacement for VLOOKUP. You can't use it for everything VLOOKUP can do, and it comes with a few limitations of its own.

However, if you've spent years building messy concatenated helper columns just to perform multi-condition lookups, discovering DGET's intuitive query structure can improve how you approach those specific tasks. It won't replace VLOOKUP in every spreadsheet, but it's one of those lesser-known Excel functions that's well worth keeping in your toolkit.