INDEX MATCH in Excel

XLOOKUP is the default, but professionals don't throw INDEX + MATCH away, and they have three concrete reasons. Knowing them marks the difference between someone who memorised one function and someone who understands lookups.

The mechanics. INDEX(array, row_num, [column_num]) returns the value at a position. MATCH(lookup_value, lookup_array, [match_type]) returns the position of a value, with match_type 0 meaning exact. Put them together and MATCH finds which row while INDEX fetches the value in that row. Here's how you get the price for the code in F2, with codes in A2:A50 and prices in C2:C50:

=INDEX(C2:C50, MATCH(F2, A2:A50, 0))

MATCH returns the row number of the code, and INDEX reads that row of the price column. Now for the three reasons it has lasted.

Why do professionals still use INDEX MATCH?

1. Left-lookups are native. MATCH searches one range and INDEX reads a completely separate one, so the value you fetch can sit anywhere relative to the key, including to its left. VLOOKUP can't do this at all. XLOOKUP handles left-lookups too, since its return array is separate as well. So this reason really reads "INDEX + MATCH or XLOOKUP, never VLOOKUP."
2. Robustness to inserted columns. INDEX + MATCH refers to whole column ranges and never to a counted offset. Insert a column in the middle of your data and the ranges shift with it automatically, so nothing breaks. VLOOKUP's hardcoded col_index_num points at the wrong column after any such insert, and it doesn't tell you. For years this was the biggest real-world reason INDEX + MATCH outlived VLOOKUP.
3. Speed and reuse on huge sheets. Say you must fetch many columns for the same key, ten fields for one customer. A single MATCH computes the row position once, and you reuse it across ten cheap INDEX calls, or store it in a LET variable. On a sheet with hundreds of thousands of rows and dozens of output columns, that "match once, index many" pattern is materially faster than dozens of separate XLOOKUPs that each re-search the key column. On very large models it matters.

INDEX MATCH vs XLOOKUP: which should you use?

XLOOKUP wins on readability and on returning arrays. INDEX + MATCH wins on the match-once pattern, and it runs on legacy Excel. A fluent analyst carries both.