INDEX and MATCH Combined
The classic, version-proof alternative to VLOOKUP and XLOOKUP alike.
Compatibility: All Excel versions
What You'll Learn
- What MATCH returns versus what INDEX returns
- How to combine INDEX and MATCH to look up a value in either direction
- Why INDEX/MATCH is still worth knowing even with XLOOKUP available
MATCH finds the position of a value within a range:
=MATCH("Total",A1:A10,0)
INDEX returns the value at a given position within a range:
=INDEX(B1:B10,5)
Combining Them
Nested together, MATCH finds the row, and INDEX retrieves the value from that row in a different column:
=INDEX(C1:C10,MATCH(A2,A1:A10,0))
This looks up A2 within A1:A10, finds its position with MATCH, then uses that position to pull the corresponding value from C1:C10 with INDEX. Unlike VLOOKUP, the lookup column and return column can be in any order relative to each other.
Why Learn This If XLOOKUP Exists?
XLOOKUP isn't available in every Excel version still in use — INDEX and MATCH work in every version going back decades, which matters if your file might be opened by someone on an older copy of Excel, or if you work somewhere that hasn't upgraded yet.
Common Mistakes
- Mixing up which function returns a position (MATCH) and which returns a value (INDEX)
- Forgetting the third argument (0) in MATCH, which is required for an exact match
Practice Exercise
Using the same product lookup table from earlier lessons, rebuild the lookup using INDEX and MATCH combined instead of VLOOKUP or XLOOKUP.
Key Takeaways
- MATCH returns a position; INDEX returns a value at a position
- Combined, they can look up a value in any column, in either direction
- INDEX/MATCH works in every Excel version, unlike XLOOKUP