Excel function guide · Lookup
INDEX and MATCH in Excel
MATCH returns the position of a value in a range, and INDEX returns the value at a given position. Together, INDEX(return_range, MATCH(value, lookup_range, 0)) is a lookup that works in every version of Excel and can return any column, including ones to the left.
Syntax
=INDEX(array, row_num, [column_num])
=MATCH(lookup_value, lookup_array, [match_type])
=INDEX(return_range, MATCH(lookup_value, lookup_range, 0))
| Argument | Required? | What it means |
|---|---|---|
| array (INDEX) | Required | The range to return a value from. |
| row_num (INDEX) | Required | Which row of array to return; in a lookup, this is MATCH's result. |
| column_num (INDEX) | Optional | Which column of array, when array has more than one column. |
| lookup_value (MATCH) | Required | The value to find. |
| lookup_array (MATCH) | Required | The single row or column to search. |
| match_type (MATCH) | Optional | 0 exact match. 1 (the default) largest value ≤ lookup_value in ascending data; -1 smallest value ≥ it in descending data. |
INDEX and MATCH examples
Every example uses this small table. The results are what Excel shows in the cell.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Product | SKU | Units | Revenue |
| 2 | Desk lamp | P-110 | 120 | 4,680 |
| 3 | Office chair | P-205 | 35 | 8,400 |
| 4 | Monitor arm | P-318 | 64 | 5,440 |
| 5 | Standing desk | P-402 | 12 | 6,240 |
| 6 | Webcam | P-517 | 88 | 5,720 |
1. Revenue for a SKU
=INDEX(D2:D6, MATCH("P-318", B2:B6, 0))Result: 5,440
MATCH finds P-318 in position 3 of B2:B6; INDEX returns the 3rd value of D2:D6.
2. Look to the left
=INDEX(A2:A6, MATCH("P-402", B2:B6, 0))Result: Standing desk
The product name is to the left of the SKU column. INDEX can return from any range, so that's no problem.
3. Two-way lookup
=INDEX(A2:D6, MATCH("P-205", B2:B6, 0), MATCH("Revenue", A1:D1, 0))Result: 8,400
One MATCH picks the row and another picks the column by its header, so the formula keeps working if columns are rearranged.
Try it now
Practise INDEX and MATCH in a live sheet.
Type each formula yourself. Answers are checked by the same engine as the full course. No account needed.
Step 1 of 3
In F3, use MATCH to find the position of the SKU in F2 within B2:B6 (exact match).
Common INDEX and MATCH mistakes
Forgetting the 0 in MATCH
MATCH defaults to match_type 1, an approximate match for sorted data. For IDs, codes and names always write 0 as the third argument.
Ranges that don't line up
INDEX(D2:D6, MATCH(…, B1:B6, 0)) is off by one row because the ranges start on different rows. Start and end both ranges on the same rows.
Treating MATCH as a row number
MATCH returns a position within the range, not the sheet row. Position 3 in B2:B6 is row 4 of the sheet.
Not locking the ranges
When you fill the formula down, lock both ranges ($D$2:$D$6, $B$2:$B$6) so only the lookup value moves.
Practise this in Excel Quest
INDEX & MATCH Members
Course 6 · Lookups & Relationships · 12 min
Find positions with MATCH, return values with INDEX, then combine them into two-way lookups on a sales matrix.
This lesson is included in Formula Quest memberships. A free account starts you on the free lessons of the Excel Quest curriculum.