How to Use the INDEX and XMATCH Functions in Google Sheets for Flexible Two-Way Lookups

While VLOOKUP remains one of the most widely used formulas in Google Sheets, its inability to look to the left, vulnerability to column insertions, and rigid syntax have long frustrated data analysts. While modern XLOOKUP solved many linear search limitations, multi-dimensional matrix lookups—where you need to locate a value by matching both a specific row header and column header simultaneously—are best handled using the modern combination of INDEX and XMATCH.

Understanding the Advantages of XMATCH Over Classic MATCH

The native XMATCH function upgrades classic matching with essential modern capabilities:

• Exact Match by Default: Classic MATCH required entering 0 as the third argument to avoid approximate matches. XMATCH defaults to exact matching automatically.
• Reverse Search: Set search mode to -1 to perform bottom-up searches, returning the last recorded transaction in a log.
• Wildcard Support: Natively matches wildcard patterns using question marks (?) and asterisks (*).

Performing a Two-Way Matrix Lookup with INDEX and XMATCH

Suppose you have a pricing matrix where product names run vertically in column A2:A20, regional tiers (US, UK, EU, JP) run horizontally across row B1:E1, and prices fill the grid B2:E20:

1. To find the price for a specific product entered in cell G2 and a region entered in H2:
2. Enter the formula:
   =INDEX(B2:E20, XMATCH(G2, A2:A20), XMATCH(H2, B1:E1))
3. Press Enter. Google Sheets evaluates both coordinates dynamically, returning the exact intersecting price.

Performing Bottom-Up Reverse Lookups

When tracking daily inventory logs where timestamps are recorded chronologically downward, you often need the most recent status entry for an item:

• Use negative search mode in XMATCH to return the latest record:
  =INDEX(C2:C100, XMATCH("Widget A", A2:A100, 0, -1))

Adopting INDEX and XMATCH in Google Sheets equips your analytical models with resilient, structural lookups that remain robust against column reordering and dataset expansions.