Formula Guide

INDEX MATCH Generator

INDEX-MATCH is the go-to formula for advanced lookups. Unlike VLOOKUP, it can look in any direction, handle multiple criteria, and is more efficient on large datasets.

What It Does

Combines INDEX (returns a value from a position) with MATCH (finds a position) to perform flexible lookups in any direction.

Syntax

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

Multi-criteria:
=INDEX(return_range, MATCH(1, (criteria1_range=criteria1)*(criteria2_range=criteria2), 0))

Example

Example: Look up price by product name
=INDEX(C2:C100, MATCH("Widget", A2:A100, 0))

Data:
| Product | SKU  | Price |
| Widget  | W001 | 29.99 |
| Gadget  | G001 | 49.99 |

Result: 29.99

Pro Tips

Use 0 as the third argument in MATCH for exact matches
INDEX-MATCH can look left, right, up, or down — unlike VLOOKUP
For multiple criteria, use array formula with MATCH
It's faster than VLOOKUP on large datasets

Try the AI INDEX-MATCH Generator

Pre-filled with: “Look up a value based on multiple criteria”

Generate any formula with AI