INDEX returns the value at a given position in a range, and MATCH finds the position of a value in a list. Combine them as INDEX(return_range, MATCH(lookup_value, lookup_range, 0)) and you have a lookup that works in any direction and in every version of Excel. Many experienced users prefer INDEX-MATCH over VLOOKUP because it's more flexible and doesn't break when columns are inserted.
INDEX: value at a position
=INDEX(range, row_number)
=INDEX(B2:B10, 3) returns the 3rd value in that range. Think of INDEX as "give me the item at this position."
MATCH: find a position
=MATCH(lookup_value, lookup_range, 0)
The 0 means exact match. =MATCH("EMP102", A2:A10, 0) returns the position of EMP102 in that list (say, 2 if it's the second item).
MATCH tells you where something is; INDEX uses that position to fetch the value.
Combine them
Put MATCH inside INDEX. MATCH finds the row, INDEX returns the value from that row in another column:
=INDEX(C2:C100, MATCH(E2, A2:A100, 0))
Read it as: find E2 in column A (MATCH), then return the value at that position from column C (INDEX).
Why use INDEX-MATCH over VLOOKUP
| Point | VLOOKUP | INDEX-MATCH |
|---|---|---|
| Direction | Left column only | Any direction |
| Column number | Fixed number, breaks if columns move | Points to the actual column |
| Speed on big data | Slower | Often faster |
| Version | All versions | All versions |
Unlike XLOOKUP, INDEX-MATCH works even in old Excel, which is why it's still widely used.
Worked example
Product codes in A, product names in B, prices in C. A code is typed in E2.
- Price:
=INDEX(C2:C50, MATCH(E2, A2:A50, 0)) - Name:
=INDEX(B2:B50, MATCH(E2, A2:A50, 0))
The same MATCH position feeds both, so you can pull several fields for one lookup value.
Look up to the left
Because MATCH finds the position and INDEX can return from any column, you can look left easily:
=INDEX(A2:A50, MATCH(E2, C2:C50, 0))
This searches column C and returns from column A, something VLOOKUP can't do.
Pro tips
- Always use
0as the third argument of MATCH for an exact match. - Insert or delete columns freely; INDEX-MATCH points to the real column, so it won't break like VLOOKUP's fixed number.
- If you're on Excel 2021/365, XLOOKUP does the same job more simply; INDEX-MATCH is the go-to for older versions.
Common mistakes
- Forgetting the 0 in MATCH. Without it, MATCH may do an approximate match and return the wrong row.
- Mismatched ranges. The INDEX return range and the MATCH lookup range should cover the same rows.
- Overcomplicating it. Read it as MATCH-finds-row, INDEX-gets-value. That mental model keeps it simple.
Key takeaways
- INDEX returns the value at a position; MATCH finds the position.
- Combine:
INDEX(return_range, MATCH(value, lookup_range, 0)). - Works in any direction and in every Excel version.
- Doesn't break when columns are inserted, unlike VLOOKUP.
Practice task
Make a table of product codes, names and prices. Using INDEX-MATCH, return the price for a code you type in a cell. Then return the name for the same code, reusing the same MATCH logic.
Learn every lookup method employers test in the ADCA program at HCI.
Frequently Asked Questions
What is INDEX and MATCH in Excel?
INDEX returns the value at a given position in a range, and MATCH finds the position of a value. Combined, they look up data in any direction.
What is the INDEX MATCH formula?
`=INDEX(return_range, MATCH(lookup_value, lookup_range, 0))`. MATCH finds the row, and INDEX returns the value from that row in the return range.
Why use INDEX-MATCH instead of VLOOKUP?
INDEX-MATCH can look in any direction, doesn't break when columns are inserted, is often faster on large data, and works in every Excel version.
What does the 0 mean in MATCH?
The 0 tells MATCH to find an exact match. Without it, MATCH may return an approximate position and the wrong result.
Is INDEX-MATCH better than XLOOKUP?
XLOOKUP is simpler but needs Excel 2021 or 365. INDEX-MATCH does a similar job and works in older versions too, so it's still widely used.