Lookup & Reference FormulaGoogle Sheets V8 Engine Verified

Google Sheets INDEX & MATCH: Two-Way Matrix Lookup Masterclass

Combining INDEX with MATCH provides two-way horizontal and vertical matrix lookups. It looks left and right, supports dynamic column and row coordinates, and is resilient against structural spreadsheet changes.

Formula Syntax:=INDEX(reference, [row], [column]) with MATCH(lookup_value, lookup_array, [match_type])
πŸ’‘ Best Practice: Always try complex formulas on a copy of your file first before applying them to active production data.

Technical Overview & Engine Mechanics

INDEX-MATCH is the classic, battle-tested two-way lookup pairing in spreadsheet engineering. By combining the INDEX function (which returns a cell value at a given row and column offset) with the MATCH function (which finds the relative position of a lookup key within an array), INDEX-MATCH provides complete freedom to search values left, right, vertically, or horizontally. For decades before XLOOKUP was introduced, INDEX-MATCH was the gold standard for robust financial modeling.

Formula Comparison: INDEX / MATCH vs. Alternatives

Compared to VLOOKUP, INDEX-MATCH is immune to column insertion breakage because it references lookup and return ranges independently. It also consumes less computational overhead on wide tables because it only references two columns rather than the entire intervening matrix. While XLOOKUP is often preferred in modern sheets for conciseness, INDEX-MATCH remains essential for backward compatibility and multi-dimensional grid lookups.

Formula Parameters & Arguments

ArgumentTypeRequiredDescription
referenceRangeRequiredThe return range or full data matrix.
rowMATCH()RequiredCalculates the target row offset coordinate.
columnMATCH()OptionalCalculates the target column coordinate for 2D matrix lookups.

Real-World Spreadsheet Examples

Explore verified operational formulas used in enterprise reporting and database workflows:

Example 1: Classic Leftward Lookup

=INDEX(A2:A100, MATCH(E2, B2:B100, 0))
How it works: Finds key E2 in Column B and returns matching value from Column A.

Example 2: 2-Way Matrix Lookup (Product Price by Tier)

=INDEX(B2:F20, MATCH("Enterprise", A2:A20, 0), MATCH("US-East", B1:F1, 0))
How it works: Finds row for Enterprise and column for US-East to retrieve regional rate.

Example 3: Lookup with Multiple Criteria

=INDEX(C2:C100, MATCH(1, (A2:A100="ProductA") * (B2:B100="Q4"), 0))
How it works: Matches across two conditions using boolean array multiplication.

Example 4: Two-Dimensional Matrix Lookup (Intersection of Row and Column)

=INDEX(B2:M100, MATCH("Enterprise Plan", A2:A100, 0), MATCH("October", B1:M1, 0))
How it works: Searches product plan on the vertical axis and month on the horizontal header axis, returning the exact pricing cell.

Example 5: Left-Hand Lookup Returning Employee Name from Badge ID

=INDEX(A2:A500, MATCH(E2, C2:C500, 0))
How it works: Looks up Badge ID in Column C and seamlessly returns Employee Name located to its left in Column A.

Common Errors & Solutions

❌ #N/A Did not find value in MATCH evaluation
Resolution: Ensure exact match parameter `0` is passed as the 3rd argument in MATCH.
❌ #N/A Value not found
Resolution: Always verify the third argument of MATCH is set to 0 for exact matching: MATCH(key, range, 0). If omitted, MATCH assumes sorted data and returns wrong offsets.
❌ #REF! Argument out of range in INDEX
Resolution: Ensure the row number returned by MATCH does not exceed the row count of the INDEX range. Both ranges should start on the same row.

Performance Pro Tips

  • INDEX/MATCH consumes less computing memory than VLOOKUP on massive 100,000+ row sheets because it only evaluates the two target columns.

Frequently Asked Questions: INDEX / MATCH

Why was INDEX-MATCH historically preferred over VLOOKUP?

It allows looking up columns to the left, does not break when columns are added, and evaluates faster on large enterprise worksheets.

Can INDEX-MATCH handle multiple search criteria?

Yes. Use an array formula match: =INDEX(C2:C100, MATCH(1, (A2:A100 = "East") * (B2:B100 = "Manager"), 0)).

Is INDEX-MATCH faster than VLOOKUP?

Yes. VLOOKUP references the entire table array, keeping every column in memory. INDEX-MATCH only references the lookup column and return column.

What does the 0 mean in MATCH(val, range, 0)?

0 enforces exact matching. 1 requires sorted ascending data; -1 requires sorted descending data.

Should I use XLOOKUP or INDEX-MATCH today?

XLOOKUP is recommended for modern sheets due to simpler syntax. Use INDEX-MATCH when designing two-way intersection grids or legacy templates.

Related Google Sheets Formulas