INDEX & MATCH — Excel guide
The power-user lookup that predates XLOOKUP and still matters everywhere compatibility does: MATCH finds the position, INDEX fetches the value at it. The two ranges are independent — that's the superpower.
MATCH
Return the POSITION of a value in a range (not the value itself).
- Always write the 0 — the default is 1 (approximate), the classic silent wrong answer.
- Wildcards work in exact mode: MATCH("App*", range, 0).
INDEX
Return the value at a given row (and column) of a range.
- Keep both ranges starting on the SAME row or everything shifts off by one.
- Row 0 means 'whole column': INDEX(A1:C10, 0, 2) spills all of column B's slice.
- INDEX returns a reference — A1:INDEX(...) builds self-adjusting ranges.
- Pulling many fields for one match? Put MATCH in a helper cell once and reuse it — the search is the expensive part.