FormulaGym
XP 0
Streak 0

INDEX and MATCH in Excel

INDEX and MATCH work as a pair to look things up in any direction, in every version of Excel.

What INDEX / MATCH does

MATCH finds the position of a value in a range, for example that Backpack is the third item. INDEX then returns the item at that position from another range. Together they do what VLOOKUP does, without needing the lookup value to be on the left.

Syntax

=INDEX(return_range, MATCH(lookup_value, lookup_range, 0))
return_range
The cells that hold the answer you want.
lookup_value
What to find.
lookup_range
The cells to search for it. It must be the same size as the return range.
0
Asks MATCH for an exact match.

Worked example

ABCDEF
1IDProductPriceLook up productPrice
21001Notebook8Backpack40
31004Desk lamp25
41007Backpack40
51010Headphones60
F2 =INDEX(C2:C5,MATCH(E2,B2:B5,0))

MATCH finds Backpack in position 3 of B2:B5. INDEX returns the third item of C2:C5, which is 40.

Now you try

A fresh exercise every time. Press Another exercise as often as you like.

Where you will use it

Finance

Look up values from tables where the key is not in the first column.

Sales & Ops

Pull prices or stock levels by name.

Data cleanup

Build lookups that survive inserted columns.

Common mistakes

  • Leaving out the 0 in MATCH. Without it MATCH looks for the nearest match, which needs sorted data.
  • Return and lookup ranges that differ in size or start on different rows, so the positions do not line up.
  • Swapping the arguments. INDEX takes the answer range first. MATCH takes the value to find first.

The INDEX / MATCH course

  1. 1
    Look up with INDEX and MATCHFree practice below
    Practise
  2. 2
    Look left of the keyComing soon
  3. 3
    Two-way lookupsComing soon
  4. 4
    Approximate matchesComing soon
  5. 5
    Dynamic rangesComing soon

Common questions

Why use INDEX and MATCH when XLOOKUP exists?

They work in every version of Excel, and you will meet them in many existing workbooks.

Does it break when I insert a column?

Less often than VLOOKUP, because it points at the columns directly instead of counting columns.

Related functions