How to Use INDEX MATCH in Excel (and Google Sheets): A Step‑by‑Step Guide
1. Understanding INDEX and MATCH
| Function | Returns | Typical use |
|---|---|---|
INDEX(array, row_num, [column_num]) |
The value from the specified row (and column) of array | Retrieve a cell’s content when you know its position |
MATCH(lookup_value, lookup_array, [match_type]) |
The relative position of lookup_value in lookup_array | Find where a value lives inside a one‑dimensional range |
How they work together
MATCHscans a column (or row) and returns the position of the item you’re looking for.INDEXuses that position to fetch the corresponding value from any column (or row) you choose.
Because MATCH gives a position instead of a value, the combo can perform left‑lookups, two‑way lookups, and dynamic column selections—something VLOOKUP can’t do without extra work.
Note: The syntax is identical in Google Sheets. In older Excel versions (pre‑2007) you must confirm the formula with Ctrl + Shift + Enter; from Excel 2007 onward a normal Enter works.
2. Preparing Your Data
- Create a clean lookup table – place the lookup column on the left‑most side and the return column(s) to its right. Avoid merged cells, blank rows, or hidden rows inside the table.
- Ensure unique keys (or decide how to handle duplicates) –
MATCHreturns the first occurrence by default. - (Optional) Convert the range to an Excel Table – select the range and press Ctrl + T. Tables expand automatically, keeping your formulas up‑to‑date.
Sample table (A2:C5)
| Product ID | Product Name | Price |
|---|---|---|
| 101 | Widget A | 12.99 |
| 102 | Widget B | 15.49 |
| 103 | Widget C | 9.75 |
| 104 | Widget D | 22.30 |
If the table occupies A2:C5 (or is named Products), a basic lookup looks like this:
=INDEX(C2:C5, MATCH(102, A2:A5, 0))
Result: 15.49 (price of product 102)
3. Basic INDEX MATCH Syntax
=INDEX(return_range, MATCH(lookup_value, lookup_range, 0))
| Part | Meaning |
|---|---|
| return_range | Column (or row) containing the values you want to return |
| lookup_value | Exact value you’re searching for |
| lookup_range | Column (or row) that holds the lookup values |
| 0 | Forces an exact match (use 0 or FALSE) |
Example
| A (Product) | B (Price) |
|---|---|
| Apple | 0.50 |
| Banana | 0.30 |
| Cherry | 0.75 |
| Date | 1.20 |
=INDEX(B2:B5, MATCH("Cherry", A2:A5, 0))
Result: 0.75
Explanation: MATCH finds “Cherry” in the third row, then INDEX returns the third entry from the price column.
Tip: Use absolute references ($A$2:$A$5) or structured table references (Table1[Price]) when copying the formula down.
4. Two‑Way Lookups (Row + Column)
When you need the value at the intersection of a specific row and column, nest two MATCH functions inside INDEX.
=INDEX(A2:D5,
MATCH("East", A2:A5, 0), // row number
MATCH("Q3", B1:D1, 0)) // column number
| Q1 | Q2 | Q3 | Q4 | |
|---|---|---|---|---|
| North | 120 | 135 | 150 | 165 |
| South | 110 | 125 | 140 | 155 |
| East | 130 | 145 | 160 | 175 |
| West | 115 | 130 | 145 | 160 |
Result: 160 (value where “East” meets “Q3”).
The same formula works in Google Sheets without modification.
5. Advanced Tips
5.1 Handling Errors
| Situation | Formula | What it returns |
|---|---|---|
| No match found | =IFERROR(INDEX(B2:B10, MATCH(E2, A2:A10, 0)), "Not found") |
"Not found" instead of #N/A |
| Distinguish “not found” from other errors (Excel 2013+) | =IFNA(INDEX(B2:B10, MATCH(E2, A2:A10, 0)), "Missing") |
"Missing" only when the lookup is absent |
Google Sheets supports IFERROR; use IFNA only in Excel.
5.2 Dynamic Ranges
- Excel Table (recommended)
=INDEX(Table1[Price], MATCH(E2, Table1[Item], 0))
- OFFSET + COUNTA (works in both Excel & Google Sheets)
=INDEX(OFFSET($B$2,0,0,COUNTA($B$2:$B$1000)),
MATCH(E2, OFFSET($A$2,0,0,COUNTA($A$2:$A$1000)), 0))
Both approaches automatically adjust as rows are added.
6. Common Mistakes to Avoid
| Mistake | Why it Happens | Fix |
|---|---|---|
| Wrong match type | Omitting the third argument defaults to an exact match, but using 1 or -1 can return the nearest value. |
Always specify 0 (or FALSE) for exact matches. |
| Mismatched data types | Numbers stored as text won’t match numeric lookup values. | Ensure both sides are the same type (VALUE(), TEXT(), or -- to coerce). |
| Incorrect orientation | Supplying a row range where a column range is expected (or vice‑versa) triggers #VALUE!. |
Keep INDEX’s first argument a single rectangular range; use MATCH on a one‑dimensional range. |
| Unlocked references when copying | Relative references shift, breaking the lookup. | Use absolute references ($A$2:$A$100) or named/table references. |
| Whole‑column references in older Excel | Excel 2007‑2016 can’t handle whole‑column arrays in array formulas, leading to #REF! or slow performance. |
Limit the range to the actual data set (e.g., A2:A500). |
7. When to Choose INDEX MATCH Over VLOOKUP
| Situation | Why INDEX MATCH wins |
|---|---|
| Lookup column isn’t the leftmost column | INDEX + MATCH can search any column. |
| Large data sets | It searches only the lookup column, reducing calculation time. |
| Columns are added/removed | MATCH can locate the correct column automatically. |
| Two‑way lookups | Combining two MATCH functions with INDEX handles row + column lookups natively. |
| Cross‑platform workbooks | Works in Excel 2007+ and Google Sheets, whereas XLOOKUP requires Excel 365/2021. |
Rule of thumb: Use VLOOKUP only for tiny tables where the lookup column is always first. Switch to INDEX MATCH as soon as you need flexibility, speed, or cross‑application compatibility.
8. FAQ
What is the difference between INDEX MATCH and VLOOKUP?
VLOOKUP can only search the leftmost column of a table and returns a value from a column to its right, while INDEX MATCH lets you search any column and return a value from any other column. This makes INDEX MATCH more flexible, faster on large data sets, and usable for left‑lookups and two‑way lookups.
Can INDEX MATCH handle multiple criteria?
Yes. Combine MATCH with an array‑formula or use INDEX together with SUMIFS/COUNTIFS. For example, {=INDEX(ReturnRange, MATCH(1, (Lookup1=Criteria1)*(Lookup2=Criteria2), 0))} returns the first row that meets both criteria (enter with Ctrl + Shift + Enter in older Excel; Google Sheets evaluates arrays automatically).
Does INDEX MATCH work in Google Sheets?
Absolutely. The function names, arguments, and behavior are the same in Google Sheets, so you can copy an Excel INDEX MATCH formula directly into Sheets and it will work.
How do I avoid #N/A errors in INDEX MATCH?
Wrap the lookup in an error‑handling function: IFERROR (Excel 2007+ and Google Sheets) or IFNA (Excel 2013+). Example: =IFERROR(INDEX(B:B, MATCH(E2, A:A, 0)), "Not found").
Can I use INDEX MATCH with dynamic named ranges?
Yes. Define a named range that expands automatically (e.g., using OFFSET + COUNTA or a Table), then reference it in the formula: =INDEX(PriceRange, MATCH(E2, ItemRange, 0)). The lookup will adjust as rows are added or removed.
Happy looking up!