Excel INDEX MATCH With Table Structured References

Formulas & functions · Updated November 19, 2026

Yes—Excel INDEX MATCH works with table structured references. For example, if a table named Inventory has columns Item and Unit Price, and the value to find is in E2, use =INDEX(Inventory[Unit Price],MATCH(E2,Inventory[Item],0)). MATCH finds the matching row in the Item column, and INDEX returns the value from the same position in Unit Price.

This approach combines a flexible lookup formula with Excel Tables, whose references use readable table and column names instead of fixed cell addresses. It is useful when data grows, columns move, or formulas need to be easier to understand. The key is to use matching table columns and set MATCH’s final argument to 0 for an exact match.

How INDEX MATCH works with a table

INDEX and MATCH do separate jobs. INDEX returns a value from a range or array at a specified position. MATCH returns the position of a lookup value within a one-dimensional range. Putting MATCH inside INDEX passes that position to INDEX, so it can retrieve a value from a different column on the same row.

In the example above, Inventory[Item] is MATCH’s lookup array and Inventory[Unit Price] is INDEX’s return array. If the item is found third in the table, MATCH returns 3 and INDEX returns the third price. The two columns must have corresponding rows in the same order; using columns from different tables, or ranges with different row order, can return an unrelated result.

The final 0 in MATCH means exact match. Without it, MATCH can use an approximate-match mode that expects sorted data and may return a surprising result when the list is unsorted. For ordinary product, employee, or code lookups, exact matching is usually the intended behavior.

Turn the source data into an Excel Table

Structured references work with Excel Tables, not just any block of cells. In Excel for Microsoft 365 on Windows, select a cell in the data and choose Insert > Table. Check that the selected range is right and that My table has headers is checked if the first row contains column names. Excel assigns a default name, which you can change on the Table Design tab in the Table Name box.

Give the table a concise, descriptive name such as Inventory, Staff, or Orders. Table names cannot contain spaces, so use a joined word or an underscore if needed. Column headings can contain spaces; Excel handles them in structured references. For instance, a column heading Unit Price is referred to as Inventory[Unit Price]. Avoid blank or duplicate headings because Excel needs distinct column names to build reliable references.

On Mac, the same general approach applies: use Insert > Table, then use the table-design controls to name the table. Excel for the web also supports tables and structured references, although the ribbon layout can differ. When rows are added directly beneath a table, Excel generally expands it and updates references to include the new data.

Write the basic formula

Suppose Inventory contains Item and Unit Price columns, and E2 contains Cable. Enter the lookup formula in a cell outside the table. Because the formula names the table columns, it does not depend on whether those columns happen to be in A and B or somewhere else in the worksheet.

With Inventory[Item] and Inventory[Unit Price] as equal-length table columns and the lookup value in E2, use:

=INDEX(Inventory[Unit Price],MATCH(E2,Inventory[Item],0))

If the table rows are Widget at 12.5, Cable at 4.25, and Adapter at 8, the result for Cable is 4.25. The formula searches for Cable in the Item column, gets the matching position, and returns the value at that position in Unit Price. The specific worksheet cells occupied by the table do not need to be included in the formula.

Structured references are not case-sensitive for ordinary text matching. If two rows contain the same item name, MATCH returns the position of the first matching row. If the lookup value does not occur in the column, the formula normally returns #N/A.

Use a lookup value from another table

A lookup cell can itself belong to a table. Assume a second table named Requests has a column named Item to look up and a column named Price Found for the result. Entering the formula in the Price Found column lets Excel use the current row’s Item value. The @ symbol in [@Item] means the Item value in this formula’s current table row; it is not a wildcard.

With the source table named Inventory and the formula entered in Requests[Price Found], use the current row’s Requests[Item] value:

=INDEX(Inventory[Unit Price],MATCH([@Item],Inventory[Item],0))

Excel normally fills a calculated-column formula down the rest of the table. As new rows are added to Requests, the formula can also extend into them. The source table and the lookup table may be on different worksheets; if the table names are unique in the workbook, the structured references can still identify them without sheet names.

For a formula outside a table, use a normal cell reference such as E2 for the lookup value. For a formula inside a table, use [@Column] when you mean the value in the current row, or a normal cell reference if the lookup value is elsewhere. Mixing those two contexts is a common source of formulas that copy down incorrectly.

What structured references change

A conventional formula might use fixed ranges such as B2:B100 and C2:C100. A structured-reference formula instead names the columns. This improves readability, and it avoids having to extend a fixed range manually when new records are appended to the table. Adding or removing table rows generally adjusts the column references automatically.

Structured references also make formulas less sensitive to column position. If someone inserts a column between Item and Unit Price, Inventory[Unit Price] still refers to the named column. This is one reason INDEX MATCH with tables can be easier to maintain than a lookup formula that depends on a hard-coded column number.

Table column names need to match their headers. If a heading is changed, Excel usually adjusts formulas that refer to it. When typing a formula, selecting a table column can help Excel insert the correct structured reference. If a name contains punctuation or unusual characters, let Excel create the reference rather than guessing at its syntax.

Handle missing values and duplicates

A missing match is not always a formula mistake. The lookup value may genuinely be absent, may contain an extra space, or may have a different underlying type. For example, the number 123 and the text value 123 can behave differently in matching. First check the source values and the lookup value; do not hide every #N/A result until you know whether missing records are expected.

To show a message rather than #N/A, you can wrap the lookup in IFNA. In this version, a missing item returns the numeric value 0, so use it only if zero is an acceptable indicator in your workbook.

With the same Inventory table and lookup value in E2, use:

=IFNA(INDEX(Inventory[Unit Price],MATCH(E2,Inventory[Item],0)),0)

IFNA handles the not-found error specifically, while IFERROR catches a broader range of errors. A broad error wrapper can conceal issues such as a misspelled table or column reference, so IFNA is often preferable when the only expected problem is a missing match. If duplicate keys are possible, decide whether the first matching row is acceptable or whether the source data needs a unique identifier.

Common problems and how to diagnose them

  • #N/A: The lookup value was not found as an exact match. Check spelling, leading or trailing spaces, text-versus-number differences, and whether the expected record is actually in the table.
  • #REF! or a formula-entry error: Verify the table name and column headings. A structured reference to a renamed or deleted column may no longer point to a valid field.
  • Wrong value returned: Confirm that the lookup and return columns belong to the intended source table, are aligned row by row, and that duplicate lookup values are not causing MATCH to return an earlier record.
  • Formula does not include new records: Check that the records were added to the table rather than just placed nearby. If necessary, resize the table using Table Design > Resize Table.
  • Unexpected approximate result: Confirm that the third MATCH argument is 0. Approximate matching is not the default you want for a typical unsorted lookup list.

Spaces can be particularly difficult to spot. A value that looks like Cable may contain a trailing space, so it will not be identical to Cable. If the issue is in source data, cleaning that data is often better than making every lookup formula more complicated. Also check whether formulas recalculate: automatic calculation is the usual setting, but a workbook set to manual calculation may need recalculation after edits.

INDEX MATCH compared with XLOOKUP

In Microsoft 365, XLOOKUP is a simpler alternative for many lookups. It can search one column and return a corresponding value from another, so it does not require nesting MATCH inside INDEX. XLOOKUP is available in Microsoft 365 and Excel 2021 or later; older Excel versions may not support it. INDEX and MATCH remain useful when a workbook needs compatibility with older releases or when readers already recognize that pattern.

With Inventory[Item] as the lookup column, Inventory[Unit Price] as the return column, and Cable in E2, the equivalent XLOOKUP is:

=XLOOKUP(E2,Inventory[Item],Inventory[Unit Price])

Both approaches can use table structured references and adjust as the table grows. XLOOKUP also has an optional argument for a not-found result. INDEX MATCH is still worth understanding because it works in older Excel versions, offers a familiar general-purpose combination, and makes the separate lookup-position and return steps explicit.

Frequently Asked Questions

Can INDEX MATCH return a value from a column to the left?
Yes. INDEX MATCH does not require the return column to be to the right of the lookup column. Name the desired table column as the INDEX array and the search column as the MATCH array.

Do table structured references update when I add rows?
Generally, yes, when the new records become part of the table. The column reference such as Inventory[Item] covers the table’s data rows, rather than a manually chosen range ending at a particular row number.

Why does my formula show #N/A even though the value looks identical?
Check for hidden spaces and for a mismatch between numbers stored as text and numeric values. Also verify the exact spelling and the table column being searched. Exact-match lookup requires the values to match, not merely look similar on screen.

Can I use INDEX MATCH with a table that has spaces in its headings?
Yes. A heading such as Unit Price is written as Inventory[Unit Price]. Spaces in column headings are allowed; the table name itself cannot contain spaces.

Does INDEX MATCH return every duplicate match?
No. With an exact MATCH, the basic formula returns the result associated with the first matching position. To retrieve multiple matching rows, use a different approach, such as FILTER in Microsoft 365 or Excel 2021 and later, or remove duplicates if the lookup key is meant to be unique.

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.