INDEX & MATCH
Find a position, then retrieve the value at that position.
By the end, you’ll be able to:
- Separate finding a position from returning a value
- Build an exact-match lookup in either direction
- Use two matches to select a row and a column
MATCH finds where; INDEX retrieves what
A product master has SKU, Name and Price in separate columns. You need the price for a selected SKU, even if the returned column is to the left of the search column in another file. Build the calculation in two small parts: locate the SKU, then retrieve the corresponding price.
=MATCH(E2,A2:A4,0) returns 2 for P102. This is the second position inside A2:A4, not worksheet row 2. =INDEX(C2:C4,2) returns 25. Combined: =INDEX(C2:C4,MATCH(E2,A2:A4,0)).
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | SKU | Name | Price | Find SKU | |
| 2 | P101 | Desk tray | 18 | P102 | |
| 3 | P102 | Cable pouch | 25 | ||
| 4 | P103 | Screen stand | 40 |
Use exact matching deliberately
=MATCH(lookup_value, lookup_array, [match_type])lookup_value is the identifier to find. lookup_array is one row or column to search. match_type controls matching; 0 requests an exact match. MATCH otherwise defaults to approximate matching, so write 0 for identifier lookups.
=INDEX(array, row_num, [column_num])array is the source range; row_num selects its relative row; column_num selects its relative column when the range has multiple columns. Positions begin at 1.
This lesson uses the ordinary array form of INDEX. Its reference form and whole-row/column return modes are additional Excel capabilities, outside these tasks.
Worked example: choose the month and product
Suppose A2:A4 contains product IDs, B1:D1 contains Jan, Feb and Mar, and B2:D4 contains demand. Put the selected product in F2 and month in F3. Use =INDEX(B2:D4,MATCH(F2,A2:A4,0),MATCH(F3,B1:D1,0)).
The first MATCH locates the product row; the second locates the month column. For P102 and Feb, the matching positions are 2 and 2. Excel retrieves the center value of the 3-by-3 demand grid. Neither MATCH should include a heading not represented in that grid.
Keep the ranges aligned
Search and return ranges must describe the same records in the same order. Searching A2:A4 but returning C3:C5 creates a one-row shift. The formula may still return a believable number, which is more dangerous than an obvious error.
- Test the first and last identifiers.
- Test an identifier that is missing; an exact MATCH returns #N/A.
- Check duplicate IDs. Exact MATCH returns the first matching position, which may not be the intended business rule.
- Fix lookup ranges with $ signs if copying the formula.
Ordinary text matching is case-insensitive. P102 and p102 match; stray spaces and inconsistent number-versus-text IDs still need investigation.
Choose a maintainable lookup
These scalar INDEX/MATCH formulas work in older desktop Excel versions as well as current Excel. They do not require Ctrl+Shift+Enter. XLOOKUP may be easier to read when every recipient has a compatible version; INDEX/MATCH remains useful when maintaining existing files.
In Excel, build MATCH in a temporary cell, verify its position, and then put it inside INDEX. This makes an unfamiliar formula easier to diagnose. The browser checker here uses exact MATCH only.
Give it a try.
Work through each exercise, check your answer and use a hint whenever you need one. Formula tasks check changed inputs, including boundary cases.
0 of 4 exercises complete
The practice checker accepts any supported formula that solves the task, including whole-column ranges where supported, lowercase function names and text labels, extra spaces, and comma or semicolon separators. Array tasks check the complete result shape and values. It is a teaching tool, not a complete Excel engine. See supported behavior.
Take this with you
MATCH returns a relative position. INDEX retrieves the value at that position. Alignment and exact-match settings make the combination reliable.
Practice in Excel
Download the course workbook, follow the tasks, save your completed .xlsx and check its supported formula tasks here. Native Excel features use a separate review checklist.
Download practice workbook Check my workbook ↗Technical references and lesson review
Original lesson content. Technical behavior checked against Microsoft documentation. Technical review: 2026-10-16. Page updated: 2026-10-19. How SheetSprout develops and checks lessons.