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.
=INDEX(reference, [row], [column]) with MATCH(lookup_value, lookup_array, [match_type])Technical Overview & Engine Mechanics
Formula Comparison: INDEX / MATCH vs. Alternatives
Formula Parameters & Arguments
| Argument | Type | Required | Description |
|---|---|---|---|
| reference | Range | Required | The return range or full data matrix. |
| row | MATCH() | Required | Calculates the target row offset coordinate. |
| column | MATCH() | Optional | Calculates 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))Example 2: 2-Way Matrix Lookup (Product Price by Tier)
=INDEX(B2:F20, MATCH("Enterprise", A2:A20, 0), MATCH("US-East", B1:F1, 0))Example 3: Lookup with Multiple Criteria
=INDEX(C2:C100, MATCH(1, (A2:A100="ProductA") * (B2:B100="Q4"), 0))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))Example 5: Left-Hand Lookup Returning Employee Name from Badge ID
=INDEX(A2:A500, MATCH(E2, C2:C500, 0))Common Errors & Solutions
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
XLOOKUP
XLOOKUP is the modern, flexible replacement for VLOOKUP and HLOOKUP. It searches left, right, up, and down, defaults to exact match, handles missing data gracefully, and can return entire rows or columns without breaking when columns are inserted.
Lookup & ReferenceIMPORTRANGE
IMPORTRANGE imports a specified cell range from another external Google Spreadsheet workbook. It provides real-time multi-sheet linking with built-in OAuth access permission controls.
Lookup & ReferenceINDIRECT
INDIRECT converts a text string into an active cell or range reference. Enables dynamic switching between sheet tabs (e.g. querying Jan, Feb, Mar depending on a dropdown selection) without re-writing formulas.