How to Use INDEX and MATCH in Excel (Step by Step)

VLOOKUP has one annoying limit: it can only look to the right. If the value you want sits to the left of your lookup column, VLOOKUP simply cannot do it. INDEX and MATCH solve that — together they look up a value in any direction, and they keep working even when someone inserts a column in the middle of your table.

Related: How to Use VLOOKUP in Excel

What INDEX-MATCH does

MATCH finds the position of a value in a list, and INDEX returns the value at a given position. Combined, MATCH tells INDEX which row to fetch, so you get a two-way lookup that does not care whether the answer is to the left or the right. It is also safer than VLOOKUP when the table changes, because there is no hard-coded column number to go stale.

The syntax

=INDEX(return_range, MATCH(lookup_value, lookup_range, 0))

  • return_range — the column (or row) the answer comes from.
  • lookup_value — the value to find, for example a product code.
  • lookup_range — the column (or row) to search in.
  • 0 — the match type: 0 means exact match — use it almost every time.

Step-by-step example

Here is a price list where the price is in column A and the code is in column B. VLOOKUP cannot fetch a price from the left, but INDEX-MATCH can.

  1. Click the cell where the answer should go.
  2. Type =MATCH(A2,$B$2:$B$7,0) in a spare cell first, to see the position it finds — this is a good way to check your ranges.
  3. Now write the full formula: =INDEX($A$2:$A$7,MATCH(A2,$B$2:$B$7,0)). INDEX takes the price column, MATCH supplies the row number.
  4. Press Enter. The price for that code appears.
  5. Drag the fill handle down to copy the formula. The dollar signs keep the ranges fixed while the lookup cell moves.

Worked example

Price Product code Product
$4.50 A1001 Notebook
$6.20 A1002 Pen set
$8.90 A1003 Stapler
$3.75 A1004 Binder

With A1003 in cell A2, MATCH finds it in row 3 of the code column, and INDEX returns the third value of the price column: $8.90. The same formula works if the columns are rearranged — that is the whole point.

Common errors

#N/A — value not found. MATCH could not find the lookup value. Check for extra spaces or numbers stored as text, exactly as with VLOOKUP. Wrap the formula in IFERROR(..., "Not found") to show a friendly message instead.

#REF! — ranges are different sizes. The return range and the lookup range must cover the same rows. If one starts at row 2 and the other at row 3, the positions do not line up.

Wrong row returned. You used a match type other than 0, or the lookup range is not sorted the way that match type expects. For exact matches, always use 0.

It works for one row, then breaks. You copied the formula down without dollar signs, so the ranges slid down a row each time. Lock the ranges with $ (for example $A$2:$A$7) and the lookup cell will move while the table stays put.

Tip

In modern Excel, XLOOKUP does this job in one function — but INDEX-MATCH works in every Excel version since 2007, including old files your colleagues still use. Learn it once and it works everywhere.

Practice file

Download the INDEX-MATCH practice file (.xlsx) — it contains the price list with the code on the right, an orders sheet with the INDEX-MATCH formulas already filled in, and a blank sheet for you to try it yourself.

Google Sheets

Google Sheets supports INDEX and MATCH with the same syntax, so the same formula works there. Sheets also has XLOOKUP now, but INDEX-MATCH remains the portable choice across both apps.

Example prices and figures are illustrative, not market data.