PlayInfoBox

Home /Utilities /How to Look Up Values With INDEX and MATCH in Excel

How to Look Up Values With INDEX and MATCH in Excel

Tap a step to follow along in order.

  1. Click the cell where you want the result

    Click an empty cell where you want the looked-up value to appear.

  2. Find the position with MATCH

    Use =MATCH(lookup_value, lookup_range, 0) to get the row number where the lookup value sits within the range.

  3. Wrap it in INDEX to return the value

    Combine the two as =INDEX(return_range, MATCH(lookup_value, lookup_range, 0)) so INDEX pulls back whatever value sits at the position MATCH found.

  4. Confirm it works looking leftward too

    Unlike VLOOKUP, the return range can sit to the left of the lookup range and the formula still works normally.

  5. Why it is useful

    It works the same way in older versions of Excel where VLOOKUP or XLOOKUP are hard to use, and it can find values freely without any restriction on where the lookup column and return column sit -- a traditional but powerful combination.

Works in any Excel version, in either direction

VLOOKUP and XLOOKUP either are not available in an older Excel install or require the table's column order to be rearranged first. INDEX and MATCH split the job into two simple functions that have existed since early versions of Excel, and because MATCH finds the position independently, the return range can sit anywhere relative to the lookup range.

It keeps working when columns are inserted or reordered

VLOOKUP references a return column by a fixed number, so inserting or deleting a column anywhere inside the lookup range quietly shifts every result without an error message. Because MATCH locates a row position dynamically rather than referencing a fixed column number, INDEX and MATCH formulas keep pointing at the correct value even after the table's layout changes.

Frequently Asked Questions

What does the "0" in MATCH's third argument mean?

It tells MATCH to find only an exact match. Leaving it blank or using a different number switches to an approximate match, which can return an unexpected result.

Can I look up more than one condition at once?

Yes, using an array formula inside MATCH can satisfy two or more conditions at once, but the formula gets more complex -- XLOOKUP's multi-condition approach can be simpler when it is available.