How to Use the INDEX and XMATCH Functions for Two-Way Lookups in Microsoft Excel

Retrieving values from multi-dimensional financial tables, shipping rate matrices, and employee salary grids frequently requires matching criteria along both vertical rows and horizontal columns simultaneously. While modern functions like XLOOKUP excel at single-dimensional vertical or horizontal searches, querying a two-dimensional matrix often leads analysts to write fragile nested lookup formulas. Pairing the traditional INDEX function with the modern XMATCH function provides a fast, resilient calculation engine for two-way matrix lookups. Understanding how to deploy INDEX and XMATCH streamlines complex data retrieval across spreadsheets.

Why Pair INDEX with XMATCH Over Classic MATCH?

For decades, spreadsheet analysts paired INDEX with MATCH. While functional, the legacy MATCH function suffered from notable limitations:

  • Default Match Mode: Legacy MATCH defaults to approximate match (1), leading to accidental calculation errors if the third argument (0 for exact match) is omitted. XMATCH defaults to exact match (0) automatically.
  • Reverse and Wildcard Searches: XMATCH natively supports reverse bottom-to-top searches (search_mode: -1) and explicit wildcard matching (match_mode: 2).
  • Performance: XMATCH utilizes modern multi-threaded calculation routines optimized for Excel’s dynamic array calculation engine.

Function Syntaxes and Mechanics

INDEX Syntax for 2D Arrays

=INDEX(array, row_num, [column_num])
  • array: The data table or range containing the values you want to retrieve.
  • row_num: The vertical row coordinate within the array.
  • column_num: The horizontal column coordinate within the array.

XMATCH Syntax

=XMATCH(lookup_value, lookup_array, [match_mode], [search_mode])
  • lookup_value: The item you are searching for.
  • lookup_array: The single row or single column range to search.
  • match_mode: Optional. Defaults to 0 (exact match).
  • search_mode: Optional. Defaults to 1 (search first-to-last).

Practical Example: Two-Way Matrix Lookup for Shipping Rates

Imagine a freight shipping rate table in range B3:F8. Rows represent Weight Zones (e.g., Zone 1, Zone 2, Zone 3 in range A3:A8), while column headers represent Service Speeds (e.g., Ground, 2-Day, Overnight in range B2:F2).

You need to look up the exact shipping rate based on a target Zone entered in cell H2 and a target Speed entered in cell H3.

The Two-Way INDEX/XMATCH Formula

=INDEX(B3:F8, XMATCH(H2, A3:A8), XMATCH(H3, B2:F2))

How it executes:

  1. XMATCH(H2, A3:A8) searches vertically down column A and returns the row index (e.g., Row 4 for “Zone 4”).
  2. XMATCH(H3, B2:F2) searches horizontally across row 2 and returns the column index (e.g., Column 3 for “Overnight”).
  3. INDEX(B3:F8, 4, 3) navigates directly to the intersection coordinate of row 4 and column 3, returning the exact freight price.

Multi-Criteria Lookups with Boolean Array Matching

When you need to look up a value matching multiple criteria along the same axis (for example, matching both Department and Location):

=INDEX(C2:C100, XMATCH(1, (A2:A100="Finance") * (B2:B100="London")))

In this formula:

  • (A2:A100="Finance") * (B2:B100="London") generates an in-memory array of 1s and 0s.
  • XMATCH(1, ...) locates the first position where both logical conditions evaluate to TRUE (1 × 1 = 1).
  • INDEX(C2:C100, ...) retrieves the corresponding salary or employee name from column C.

Key Differences Between INDEX/XMATCH and Nested XLOOKUP

  • Nested XLOOKUP Alternative: You can achieve two-way lookups with nested XLOOKUP:
    =XLOOKUP(H3, B2:F2, XLOOKUP(H2, A3:A8, B3:F8))
  • Readability and Structure: INDEX/XMATCH clearly defines the coordinates as separate (Row, Column) arguments, which many financial auditors prefer over nested lookup functions when reviewing complex models.
  • Entire Row or Column Extraction: In INDEX, setting column_num to 0 returns the entire row, while setting row_num to 0 returns the entire column as a dynamic spill array, perfect for feeding into secondary statistical calculations.

Best Practice Tips for Workbook Integrity

  • Wrap with IFERROR or XMATCH Fallback: If user inputs might not exist in the matrix, wrap formulas in IFERROR(..., "Rate Not Found") to maintain clean dashboard presentation.
  • Use Data Validation: Constrain lookup input cells with dropdown menus matching header ranges to eliminate typing errors.

Get the best tech tips delivered straight to your inbox.

Join thousands of readers mastering Apple, Google, Microsoft, and Linux.