How to Use the INDEX and XMATCH Functions Together in Google Sheets for Bidirectional Matrix Lookups

Standard data lookup functions in Google Sheets—such as VLOOKUP and XLOOKUP—are engineered primarily for one-dimensional searching. They scan vertically down a key column to retrieve data from a fixed horizontal offset. However, financial matrices, pricing schedules, employee shift rotas, and shipping rate tables frequently store data in a two-dimensional grid where target values depend simultaneously on both a vertical row coordinate (e.g. Product Model) and a horizontal column coordinate (e.g. Region or Month). Nesting the modern XMATCH function within INDEX creates a flexible, bidirectional lookup formula that retrieves matrix intersections dynamically without hardcoding column index numbers.

Why INDEX and XMATCH Surpass Traditional VLOOKUP and HLOOKUP

While legacy spreadsheet users relied on VLOOKUP combined with MATCH, this approach suffers from significant vulnerabilities:

• Column Insertion Resilience: Traditional VLOOKUP breaks whenever new columns are inserted into the source data table. In contrast, INDEX references an isolated matrix range independently of column positions.
• Zero Right-to-Left Restrictions: XMATCH can search in any direction (top-to-bottom, bottom-to-top, left-to-right, or right-to-left) without requiring lookup keys to sit in the leftmost column.
• Default Exact Matching: Unlike legacy MATCH which required specifying 0 for exact matches (risking silent approximation errors if omitted), XMATCH defaults to exact matching automatically.

Structuring a Two-Dimensional Matrix Table

Consider a standard international shipping fee matrix where rows represent Destination Countries and columns represent Package Weight Tiers:

Country (A2:A6)	1kg (B1)	5kg (C1)	10kg (D1)	25kg (E1)
France	£12	£24	£38	£70
Germany	£14	£26	£42	£75
Japan	£22	£48	£85	£160
Australia	£25	£52	£92	£175

The data grid of numerical rates resides in range B2:E5.

Constructing the Bidirectional Formula

The syntax for INDEX requires three arguments:

=INDEX(reference, 
, [column])

We replace the static

and [column] numbers with dynamic XMATCH lookups.

Step 1: Locating the Row Number

Assume cell G2 contains the search country (“Japan”). Calculate its vertical position inside A2:A5:

=XMATCH(G2, A2:A5)

This returns 3, because Japan is the third entry in the country list.

Step 2: Locating the Column Number

Assume cell H2 contains the target weight tier (“10kg”). Calculate its horizontal position inside B1:E1:

=XMATCH(H2, B1:E1)

This returns 3, because 10kg is the third column header in the grid.

Step 3: Combining into the Two-Way Lookup Formula

Nest both XMATCH functions directly into INDEX pointing to the data matrix B2:E5:

=INDEX(B2:E5, XMATCH(G2, A2:A5), XMATCH(H2, B1:E1))

Google Sheets evaluates the coordinates: row 3, column 3 of the matrix. The formula instantly returns £85.

Handling Approximate Range Matching for Tiered Boundaries

In many real-world scenarios, an exact weight match is impossible (for instance, a parcel weighing 7kg). XMATCH provides an optional match_mode parameter:

• 0: Exact match (default).
• 1: Exact match or next larger item.
• -1: Exact match or next smaller item.
• 2: Wildcard character match (*, ?).

To automatically bill a 7kg parcel at the next higher tier (10kg), set the column match_mode to 1:

=INDEX(B2:E5, XMATCH(G2, A2:A5, 0), XMATCH(H2, B1:E1, 1))

Safeguarding Against Errors with IFERROR

If an operator enters a non-existent country or invalid weight parameter, unhandled formulas return an unsightly #N/A error. Wrap the expression in IFERROR to display friendly guidance:

=IFERROR(
  INDEX(B2:E5, XMATCH(G2, A2:A5), XMATCH(H2, B1:E1)), 
  "Invalid Country or Weight Tier"
)

Combining INDEX with dual XMATCH functions provides a robust, future-proof mechanism for querying complex two-dimensional datasets across Google Sheets.