Excel INDEX MATCH Function (Combined Lookup) – Examples & Practice
Practice INDEX and MATCH together online with an interactive grid, instant feedback, and clear formula help.
Instruction
Combine INDEX and MATCH to return the department for employee ID 1003.
Formula Syntax
=INDEX(array, row_num, [column_num])
- array / return_range: Values to return from (one column in the simple pattern).
- row_num: Position produced by MATCH (or a literal row index).
- lookup_value: The key MATCH searches for.
- lookup_array: The column (or row) that contains keys.
- [match_type]: Use 0 for exact match in most business lookups.
What it does
MATCH finds the relative position of a key in a range. INDEX returns the value at that position from another parallel range. Together they reproduce flexible lookups without locking you to “leftmost key” layouts.
Excel INDEX MATCH Function Examples
Row lookup (classic)
=INDEX(C2:C6, MATCH(1003, A2:A6, 0))
Finds the row where A equals 1003, then returns the value from the same position in C (for example Department).
Two-column key with helper
With helper =A2&"|"&B2 in D2, use =INDEX(E$2:E$6, MATCH(G$1&"|"&G$2, D$2:D$6, 0)) to match composite keys.
Two-dimensional INDEX
=INDEX(A2:C6, MATCH("Noah", B2:B6, 0), 3)
Returns column 3 from the row where column B equals “Noah” when the table is contiguous.
employee-table.xlsx
| A | B | C | |
|---|---|---|---|
| 1 | ID | Name | Department |
| 2 | 1001 | Liam | Design |
| 3 | 1002 | Mia | Sales |
| 4 | 1003 | Noah | Finance |
| 5 | 1004 | Olivia | Support |
| 6 | 1005 | Paul | Engineering |
| 7 | Output |
Input Formula
Need Help?
Tips
- Keep key columns and return columns the same height.
- TRIM and LOWER keys when matching messy imports.
- Move to **XLOOKUP** when your organization standardizes on Microsoft 365.
INDEX MATCH Function Use Cases
- Lookups where the return column is left of the key column
- Models that must survive column insert/delete
- Dynamic column choice with MATCH on headers
- Teaching robust spreadsheet engineering
- Stepping stone to XLOOKUP on older Excel
Common mistakes - INDEX MATCH function not working
INDEXrange height not matchingMATCHrange height- Omitting
0in MATCH and getting approximate behavior by accident - Text vs number mismatches on IDs
- Absolute vs relative references breaking copies
- Whole-column references slowing calculation
FAQ
Why use INDEX MATCH instead of VLOOKUP?
You can return columns to the left of the lookup column, avoid fragile col_index_num after inserts, and sometimes simplify large models.
What does the 0 in MATCH mean?
Match_type 0 means exact match. It is the most common choice for IDs and text keys.
Can MATCH work on unsorted lists?
Yes with match_type 0. Approximate modes require sorted lookup vectors.
Why #REF! or #N/A?
Ranges are mis-sized, MATCH returns an error, or the key is missing—check shapes and TRIM keys.
Is INDEX MATCH one function?
No. It is a pattern that nests MATCH inside INDEX to compute the row (and optionally column) index.
Comparison
| Pattern | Flexibility | Learning curve |
|---|---|---|
| INDEX/MATCH | High | Medium |
| VLOOKUP | Medium | Low |
| XLOOKUP | High | Low (when available) |
Example
=INDEX(C2:C6, MATCH(1003, A2:A6, 0))
Advanced examples
INDEX MATCH MATCH (two axes)
Use a second MATCH for column headers to return a cell from a matrix—powerful for budget cubes.
Array-friendly IF wrapper
=IFERROR(INDEX(C2:C6, MATCH(1003, A2:A6, 0)), "Missing")
Improves user-facing templates when keys can be absent.
Related functions