Excel INDEX-MATCH Combination
last modified October 5, 2026
The INDEX-MATCH combination is a powerful lookup technique in Excel.
It overcomes the main limitations of VLOOKUP and provides far more flexibility.
This tutorial covers the basics and syntax of both functions, the difference
between exact and approximate matching, and practical examples for vertical,
horizontal, two-way, left, wildcard, and approximate lookups. You will also see
how INDEX-MATCH compares with VLOOKUP and XLOOKUP and how to troubleshoot the
most common errors.
INDEX-MATCH Basics
INDEX returns a value from a specific position in a range or array.
MATCH finds the position of a lookup value inside a range. Used
together, they form a dynamic lookup: MATCH supplies the position and INDEX
returns the value at that position.
| Function | Role | Syntax |
|---|---|---|
| INDEX | Returns the value at a row/column intersection | =INDEX(array, row_num, [column_num]) |
| MATCH | Returns the position of a lookup value in a range | =MATCH(lookup_value, lookup_array, [match_type]) |
The table above shows how the two functions divide the work. INDEX needs a position, and MATCH calculates that position at run time.
| MATCH argument | Meaning |
|---|---|
| lookup_value | The value to find. It can be a number, text, logical value, or a cell reference. |
| lookup_array | The single row or column of cells to search. |
| match_type | 1 or omitted for approximate ascending, 0 for exact, -1 for approximate descending. |
| INDEX argument | Meaning |
|---|---|
| array | The range or array that contains the value to return. |
| row_num | The row within array. For a single column, this is the only position needed. |
| column_num | Optional. The column within array. Omit it for a one-column range. |
MATCH returns a position, never the value itself. For example,
=MATCH("b", {"a","b","c"}, 0) returns 2. That position is exactly
what INDEX expects as its row_num or
column_num argument.
Exact vs Approximate MATCH
The match_type argument controls how MATCH searches.
Getting it right is the most important part of building a reliable INDEX-MATCH
formula.
| match_type | Behavior | Sort requirement |
|---|---|---|
| 0 | Exact match. Supports wildcards. Best default for lookups. | None |
| 1 or omitted | Largest value less than or equal to lookup_value. | Ascending |
| -1 | Smallest value greater than or equal to lookup_value. | Descending |
The default match_type is 1, not 0, so an exact lookup must pass
0 explicitly. Omitting the third argument is a common source of
wrong results because Excel then performs an approximate match on data that is
usually not sorted. If MATCH cannot find a match, it returns the
#N/A error. With approximate matching, a lookup value smaller than
the smallest item in an ascending list also returns #N/A.
=MATCH(102, A2:A4, 0) ' exact, returns 2 =MATCH(85, A2:A6, 1) ' approximate ascending, returns 4 =MATCH(85, A2:A6, -1) ' approximate descending, requires descending data
Basic Vertical Lookup
This example demonstrates a simple vertical lookup using INDEX-MATCH as a VLOOKUP alternative.
| A | B | C |
|---|---|---|
| ID | Name | |
| 101 | John | |
| 102 | Sarah | |
| 103 | Mike | |
| =INDEX(B2:B4, MATCH(102, A2:A4, 0)) |
The table shows employee data with ID and Name columns. The formula looks up the name for ID 102 using INDEX-MATCH combination.
=INDEX(B2:B4, MATCH(102, A2:A4, 0))
This formula first finds the position of 102 in A2:A4 (position 2), then returns the corresponding value from B2:B4. The result will be "Sarah".
Horizontal Lookup
INDEX-MATCH can perform horizontal lookups, which HLOOKUP does but with more flexibility. This example shows how.
| A | B | C | D |
|---|---|---|---|
| Month | Jan | Feb | Mar |
| Sales | 1500 | 1800 | 2100 |
| =INDEX(B2:D2, MATCH("Feb", B1:D1, 0)) |
The table contains monthly sales data arranged horizontally. The formula looks up February sales using INDEX-MATCH combination.
=INDEX(B2:D2, MATCH("Feb", B1:D1, 0))
MATCH finds "Feb" in B1:D1 (position 2), then INDEX returns the corresponding value from B2:D2. The result will be 1800. This shows horizontal lookup capability.
Two-Way Lookup
INDEX-MATCH can perform two-way lookups by combining row and column searches. This example demonstrates this powerful feature.
| A | B | C | D |
|---|---|---|---|
| Math | Science | History | |
| John | 85 | 90 | 78 |
| Sarah | 92 | 88 | 95 |
| Mike | 78 | 85 | 82 |
| =INDEX(B2:D4, MATCH("Sarah", A2:A4, 0), MATCH("Science", B1:D1, 0)) |
The table shows student grades across subjects. The formula finds Sarah's Science grade using two MATCH functions for row and column positions.
=INDEX(B2:D4, MATCH("Sarah", A2:A4, 0), MATCH("Science", B1:D1, 0))
The first MATCH finds Sarah's row (2), the second finds the Science column (2). Within the range B2:D4 that intersection is worksheet cell C3. The result is 88. This demonstrates matrix-style lookups.
Left Lookup
Unlike VLOOKUP, INDEX-MATCH can look left. This example shows how to retrieve values from columns to the left of the lookup column.
| A | B | C |
|---|---|---|
| Name | ID | |
| John | 101 | |
| Sarah | 102 | |
| Mike | 103 | |
| =INDEX(A2:A4, MATCH(102, B2:B4, 0)) |
The table has Name and ID columns with ID as the lookup column. The formula finds the name for ID 102, looking left from the ID column.
=INDEX(A2:A4, MATCH(102, B2:B4, 0))
MATCH finds 102 in B2:B4 (position 2), INDEX returns corresponding name from A2:A4. Result is "Sarah". This solves VLOOKUP's left lookup limitation.
Approximate Match
INDEX-MATCH can perform approximate matches like VLOOKUP. This example shows how to find a value in a range using approximate matching.
| A | B | C |
|---|---|---|
| Score | Grade | |
| 0 | F | |
| 60 | D | |
| 70 | C | |
| 80 | B | |
| 90 | A | |
| =INDEX(B2:B6, MATCH(85, A2:A6, 1)) |
The table shows grade thresholds. The formula finds the grade for score 85 using approximate match (1 as third argument in MATCH).
=INDEX(B2:B6, MATCH(85, A2:A6, 1))
MATCH finds the largest value less than or equal to 85 (80 at position 4).
INDEX returns the corresponding grade "B". This works like a VLOOKUP approximate
match but is more flexible. The lookup array must stay sorted in ascending order,
otherwise the position returned is meaningless. A score below 0 would return
#N/A because no threshold is small enough.
Wildcard Lookup
When match_type is 0, MATCH accepts the wildcard
characters * and ?. An asterisk matches any sequence
of characters, and a question mark matches any single character.
| A | B |
|---|---|
| Product | Price |
| Laptop Pro | 1200 |
| Smartphone X | 699 |
| Tablet Air | 450 |
| =INDEX(B2:B4, MATCH("*phone*", A2:A4, 0)) |
The table lists products and prices. The formula searches for any product name that contains "phone".
=INDEX(B2:B4, MATCH("*phone*", A2:A4, 0))
MATCH finds "Smartphone X" at position 2, so INDEX
returns 699. To search for a literal asterisk or question mark, escape it with a
tilde, for example "~*". Wildcards work only with exact matching;
they are ignored when match_type is 1 or -1.
INDEX-MATCH vs VLOOKUP vs XLOOKUP
All three tools can look up data, but they differ in direction, safety, and availability. The table below summarizes the trade-offs.
| Feature | INDEX-MATCH | VLOOKUP | XLOOKUP |
|---|---|---|---|
| Lookup direction | Any direction | Right only | Any direction |
| Safe when columns are inserted | Yes | No, the column index breaks | Yes |
| Exact match by default | No, pass 0 | No, pass FALSE | Yes |
| Not-found handling | #N/A, wrap in IFNA | #N/A | if_not_found argument |
| Return several columns | Yes, with an array | No | Yes |
| Availability | All versions | All versions | Excel 2021 and 365 |
INDEX-MATCH remains the most compatible general-purpose lookup because it works in every version of Excel. XLOOKUP is simpler when it is available. VLOOKUP is easiest to teach but the least flexible.
INDEX-MATCH Errors and Troubleshooting
| Cause | Symptom | Fix |
|---|---|---|
| Lookup value does not exist | #N/A error | Check spelling and data types, or wrap the formula in IFNA |
| match_type omitted | Unexpected value from an approximate match | Pass 0 explicitly for an exact match |
| Approximate match on unsorted data | Wrong position returned | Sort the lookup array ascending, or use match_type 0 |
| Ranges of different sizes | #N/A or a shifted result | Keep lookup_array and array the same height or width |
| row_num or column_num outside array | #REF! error | Verify the dimensions passed to INDEX |
| Numbers stored as text | #N/A even though the value looks present | Make both sides the same type, for example with VALUE |
| Searching for a literal * or ? | An unintended match | Escape the character with a tilde, for example "~*" |
A common defensive pattern is =IFNA(INDEX(...), "not found"), which
replaces the raw error with a readable message.
The INDEX-MATCH combination is one of the most valuable lookup
patterns in Excel. It returns values from any direction, survives column
insertions, and works in every version of the program. By keeping
match_type at 0 for exact matches and sorting data for approximate
matches, you can avoid the errors that trip up most users. Once you are
comfortable with the pattern, you can extend it to two-way lookups, wildcard
searches, and dynamic references. Mastering INDEX-MATCH will make your
spreadsheets more robust and your lookups far more flexible.
Author
List all Excel Formulas.