How it works.
INDEX & MATCH is a classic power-user technique that combines two formulas. MATCH searches a range for a lookup value and returns its numeric position. INDEX takes that position and retrieves the corresponding value from another range. This is the historical alternative to VLOOKUP that allows searching to the left and is more resource-efficient.
💡 In simple terms.
MATCH finds the row number of your lookup item. INDEX uses that row number to grab the corresponding value from another column.
"Think of MATCH like a detective finding the row number of a suspect, and INDEX like a crane going directly to that row number to pick up the package."
Syntax guide.
Parameters
The range containing the value you want to return.
The value you want MATCH to search for.
The range that MATCH searches in.
0 = Exact match (recommended), 1 = Less than, -1 = Greater than.
Default: 0Interactive formula builder.
Fill in the input arguments below to construct your custom formula string. Copy it directly to paste into your Excel spreadsheets.
Best Practices
- INDEX-MATCH works perfectly looking left, unlike VLOOKUP which can only search to the right.
- It is excellent for two-way lookups (looking up both row and column titles) by nesting a second MATCH function inside the column argument of INDEX.
Common Gotchas
- Be careful to match the boundaries of the ranges. If INDEX is A2:A6 and MATCH is searching B1:B6, the offset will cause incorrect values to be returned.