Excel DGET vs INDEX MATCH: Which Should You Use?

Formulas & functions · Updated November 19, 2026

For a normal lookup by one key, INDEX MATCH is usually the straightforward choice; DGET is useful when you want to define database-style criteria and retrieve a value only when exactly one record matches. With IDs in A2:A6, sales in C2:C6, and the lookup ID 103 in G2, the formula =INDEX(C2:C6,MATCH(G2,A2:A6,0)) returns 310.

The quick difference

DGET and INDEX MATCH can both retrieve a value from a table, but they express the lookup differently. DGET takes a database range, a field to return, and a separate criteria range. INDEX MATCH combines INDEX, which returns a value from a range, with MATCH, which finds the position of a lookup value in another range.

The distinction matters most when the data contains more than one possible match. INDEX MATCH returns the first matching position it finds when MATCH is set to exact matching. DGET, by contrast, expects the criteria to identify exactly one record: it returns an error if there are no matches or more than one. Neither formula automatically resolves ambiguous duplicate records in a way that should be treated as a business rule.

  • Choose INDEX MATCH for a familiar lookup against a key column, especially if you want to control the lookup and return ranges separately.
  • Choose DGET when your source is arranged as a database table and its criteria range makes the conditions especially clear.
  • Check uniqueness before relying on either formula: INDEX MATCH silently returns the first match, while DGET flags multiple matches with an error.

Background: what these functions are designed to do

DGET belongs to Excel’s database-function family. Its design assumes a list with a header row and records beneath it. You specify the whole list, the field (column) to return, and criteria written in a small range on the worksheet. This makes the criteria visible and editable without embedding every condition in the formula.

INDEX and MATCH are general-purpose functions rather than a database-specific pair. MATCH finds a position in a range; INDEX returns the item at a position in a range. Because the lookup range and return range are independent, the return column can be to the left or right of the lookup column. For a simple one-column lookup, the combination is compact and easy to adapt.

Both options are established Excel functions and are available in Microsoft 365 for Windows. These examples use standard comma-separated formula syntax. DGET is not a newer substitute for INDEX MATCH, nor is INDEX MATCH a special database function; they solve overlapping tasks using different layouts and rules.

How DGET works

The DGET syntax is =DGET(database,field,criteria). The database is the full table, including its header row. The field identifies the column whose value you want, either by its header name in quotation marks or by its position within the database range. The criteria argument is a worksheet range containing header labels and one or more condition rows.

For example, suppose A1:C6 contains headers ID, Region, and Sales, followed by five records. E1:F2 contains the headers Region and ID, with East and 103 beneath them. With that layout, =DGET(A1:C6,"Sales",E1:F2) returns 310, because exactly one row has both Region equal to East and ID equal to 103.

In a criteria range, conditions in the same row are combined with AND: a record must meet all of them. Conditions on separate rows are alternatives, or OR conditions. This can make DGET convenient for criteria such as a region plus a product, or a name plus a status, without writing the conditions into a long formula. The criteria headings should match the corresponding database column headings.

Criteria can also use comparison conditions, such as greater than or equal to a value, entered in the criteria area. That is useful when the question is about records meeting a threshold rather than a single exact text key. For text criteria, Excel’s database criteria support wildcard matching; be deliberate with wildcards because a broader condition can unexpectedly match multiple records and cause DGET to return an error.

How INDEX MATCH works

MATCH searches a range and returns a position, not the matched value itself. In =MATCH(G2,A2:A6,0), the final argument 0 asks for an exact match. If ID 103 is in the third position of A2:A6, MATCH returns 3. INDEX then uses that position in the sales range to return the third sales value.

With the example data laid out as IDs in A2:A6, sales in C2:C6, and the lookup ID in G2, =INDEX(C2:C6,MATCH(G2,A2:A6,0)) returns 310. The two ranges must align row for row: the first ID in A2:A6 corresponds to the first sales value in C2:C6, and so on. If those ranges start or end on different records, the result can be wrong even though the formula has no syntax error.

INDEX MATCH is useful when the lookup column is not the first column in the source area or when you want a formula that is not tied to a fixed column number. It also allows the return range to be selected separately. However, the formula as shown returns one value and, where duplicate keys exist, an exact MATCH ordinarily finds the first occurrence. It does not report that the key is duplicated.

Choosing between them for multiple criteria

This is one of the clearest reasons to consider DGET. Its criteria range can show multiple conditions in worksheet cells, and the same-row AND rule is easy to inspect. In the earlier example, East and 103 together identify a single row. If the criteria were only Region = East and two records were in the East region, DGET would not choose one of them: it would return #NUM! because multiple records meet the criteria.

A basic INDEX MATCH formula searches one lookup range for one lookup value. To look up a combination of fields with this pattern, you generally need to create a combined key in a helper column, use a more advanced formula, or use another lookup approach. A helper key can be a sensible, transparent solution, but it must be constructed consistently for both the source data and the search value. If the data needs a clearly visible, editable criteria grid, DGET may be easier to maintain.

Do not assume that DGET is automatically the best multi-condition solution. If the intended result is a list of every matching record, DGET is the wrong fit because it is designed to return a single field value from exactly one matching record. Use a filtering or extraction method instead, depending on your Excel version and whether you need a single answer or a spill of multiple rows.

Errors, duplicates, and common problems

DGET’s two common matching errors convey different situations. No record meeting the criteria returns #VALUE!, while more than one matching record returns #NUM!. When either appears, inspect the criteria labels and values, then check how many records actually meet the conditions. A typo in a criteria header, a text-versus-number mismatch, or an overly broad condition can explain an unexpected result.

With INDEX MATCH, a lookup value that is absent from the search range typically gives #N/A. That is different from a duplicate key: duplicates do not necessarily produce an error, because MATCH returns a position. If duplicates are possible, decide whether the first occurrence is acceptable or whether the data must be cleaned, checked for uniqueness, or handled with a formula designed to return multiple matches.

  • For DGET, confirm the database includes its headers and that the criteria range uses the intended header names.
  • For INDEX MATCH, confirm the MATCH argument is 0 for exact matching and that the lookup and return ranges have corresponding rows.
  • For either method, verify that IDs are stored consistently. A number stored as text may not behave like a numeric ID in a comparison.
  • Check for duplicate keys or criteria combinations before treating a returned value as uniquely meaningful.

Adding IFERROR can hide an error, but it does not fix the cause. In particular, replacing every DGET error with a blank would conceal the important difference between no match and multiple matches. Prefer to diagnose the data or show a meaningful message that fits the situation.

Readability, maintenance, and performance

Formula readability depends on the workbook. A short INDEX MATCH formula can be easy for spreadsheet users to recognize, but readers must understand that MATCH supplies a position to INDEX. DGET can make complex conditions easier to see because they sit in a criteria grid, though it requires the extra setup of that grid and a table with suitable headers.

Neither function is universally faster in every workbook. Performance depends on factors such as the amount of data, how many formulas recalculate, and the structure of the workbook. For a small table, choose mainly for correctness and maintainability. In a large workbook, avoid unnecessary full-column calculations where a well-sized range or Excel Table can make the intended data boundaries clearer, and measure the workbook’s actual behavior rather than assuming one formula will always win.

When maintaining formulas, keep headers stable and avoid moving only one of the paired lookup ranges. DGET’s criteria area should remain understandable to the next person editing the sheet. INDEX MATCH ranges should continue to cover matching rows as the data grows; converting the source into an Excel Table and using structured references can help with expansion, although the exact formula design will differ.

Alternatives and a practical rule

For Microsoft 365 and Excel 2021 or later, XLOOKUP is often a simpler alternative for a single-key lookup: it combines the search and return behavior in one function and can specify what to show when no match is found. It still does not automatically solve every multi-match requirement. If you want all rows that meet conditions rather than one value, FILTER is a better category of tool in versions that support it. DGET remains relevant where a criteria range is useful and exactly one matching record is expected.

A practical choice is to start with the question the formula must answer. If the request is “find the sales for ID 103,” use an exact INDEX MATCH (or a suitable newer lookup function). If the request is “return the sales for the record meeting these visible database conditions, and treat zero or multiple matches as a problem,” DGET expresses that rule directly. If the request is “show every record meeting these conditions,” use a method designed to return multiple rows instead.

Frequently Asked Questions

Is DGET better than INDEX MATCH?
Neither is categorically better. INDEX MATCH is often more convenient for a straightforward lookup by one key. DGET is useful when you want database-style criteria in a separate range and need exactly one record to match.

What happens if DGET finds more than one match?
DGET returns #NUM! when multiple records satisfy its criteria. It does not return the first match or choose a record for you. Make the criteria more specific or use a method intended to return multiple results.

Does INDEX MATCH return the first match?
With MATCH’s final argument set to 0, it searches for an exact match and returns the position of the first matching item it finds. If duplicate keys exist, the resulting INDEX value corresponds to that first match.

Can DGET use more than one condition?
Yes. Put each condition’s column heading in the criteria range and place conditions that must all be true on the same row. Put alternative condition rows separately to represent OR logic. The complete criteria still needs to identify exactly one record for DGET to return a value.

Why does DGET return #VALUE! when I expect a match?
That error usually means no record met the criteria. Check that the criteria headings match the database headings, that the criteria values match the stored data, and that numbers and text have not been mixed. Also verify that the criteria range points to the intended cells.

Related

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.