What INDEX MATCH Does
INDEX MATCH is a two-function combo that looks up a value much like VLOOKUP but without its limitations. MATCH finds the row position of your search key, and INDEX returns the value at that position from any column you choose. It can look left, handle column insertions gracefully, and works with very large datasets.
Syntax
=INDEX(return_range, MATCH(search_key, lookup_range, 0))
| Parameter | Description |
|---|---|
| return_range | The column (or range) from which you want the result. |
| search_key | The value you are searching for. |
| lookup_range | The column to search within. Must be the same height as return_range. |
| 0 | Match type. Use 0 for exact match, 1 for less-than-or-equal (sorted ascending), -1 for greater-than-or-equal (sorted descending). |
Basic Examples
Example 1: Look Up an Employee's Department
| A | B | C | |
|---|---|---|---|
| 1 | Emp ID | Name | Department |
| 2 | 101 | Anna | Engineering |
| 3 | 102 | Brian | Sales |
| 4 | 103 | Clara | Marketing |
=INDEX(C2:C4, MATCH(102, A2:A4, 0))
Result: Sales
Example 2: Left Lookup (something VLOOKUP can't do)
You need to find a product name from its SKU, but the name column is to the left of the SKU column.
| A | B | |
|---|---|---|
| 1 | Product | SKU |
| 2 | Laptop | SKU-441 |
| 3 | Mouse | SKU-228 |
=INDEX(A2:A3, MATCH("SKU-228", B2:B3, 0))
Result: Mouse
Advanced Examples
Two-Way Lookup
Find a value at the intersection of a specific row and column. Suppose you have quarterly revenue by region:
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Region | Q1 | Q2 | Q3 | Q4 |
| 2 | North | 5000 | 6200 | 5800 | 7100 |
| 3 | South | 4300 | 4900 | 5100 | 5600 |
To get Q3 revenue for "South":
=INDEX(B2:E3, MATCH("South", A2:A3, 0), MATCH("Q3", B1:E1, 0))
Result: 5100
Multiple Criteria Lookup
Look up a value using two conditions (e.g., find the price where product is "Widget" AND size is "Large") by concatenating criteria with MATCH on an array:
=INDEX(C2:C100, MATCH(1, (A2:A100="Widget")*(B2:B100="Large"), 0))
Press Ctrl+Shift+Enter or wrap with ARRAYFORMULA if needed. This multiplies two TRUE/FALSE arrays together so only the row matching both conditions equals 1.
Common Mistakes
- Mismatched range heights. The lookup_range and return_range must have the same number of rows.
MATCH(x, A2:A10, 0)paired withINDEX(C2:C20, ...)will return the wrong row. - Using the wrong match type. Always use
0for exact match. The default (1) requires sorted data and rarely does what you expect on unsorted lists. - Forgetting to lock ranges. When copying the formula down, use
$to anchor ranges (e.g.,$A$2:$A$100) so they don't shift.
SheetAI Tip
Don't want to memorize this syntax? With SheetAI, just type what you need in plain English — like "find the department for employee 102" — and the formula is generated for you. Install SheetAI to try it free.