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])

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.

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.

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

Does INDEX / MATCH recalculate automatically when data changes?

Yes. Google Sheets recalculates all dependent formulas immediately whenever referenced input cells are modified.

Is this formula compatible with Microsoft Excel?

Modern functions like XLOOKUP, FILTER, UNIQUE, SORT, and LET are natively supported in Excel 365. However, QUERY is unique to Google Sheets and uses the Google Visualization API syntax.

Related Google Sheets Formulas