Lookup
INDEX and MATCH in Excel:how to combine them
MATCH gives the position of a value and INDEX returns the value at that position. Together they look up in any direction. In Spanish Excel they are called INDICE and COINCIDIR. Syntax: =INDEX(array, MATCH(lookup_value, lookup_array, 0)).
Syntax
=INDEX(array, MATCH(lookup_value, lookup_array, 0))In English Excel, arguments are separated with a comma (,). In Spain and much of Europe and Latin America, Excel uses a semicolon (;).
Arguments
| Argument | What it is |
|---|---|
array (INDEX) | Required. The column the result comes from. Example: D2:D6. |
row_num (INDEX) | Required. The position of the value you want, counting from 1. MATCH goes here. Example: 4. |
column_num (INDEX) | Optional. Only if array has several columns: which column holds the value. For a two-way lookup, a second MATCH goes here. |
lookup_value (MATCH) | Required. What you look for. Example: "P-104". |
lookup_array (MATCH) | Required. The column (or row) where you look for it. Example: A2:A6. |
match_type (MATCH) | Optional, but type 0. 0 looks for the exact value. If you leave it out, Excel uses 1: it looks up bands and needs a sorted list. |
How do you combine INDEX and MATCH? A step-by-step example
MATCH answers "which row is it in?" and INDEX answers "what is in that row?" Together they do what VLOOKUP does, without its limits. It is the same stationery catalog:
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Code | Product | Category | Price |
| 2 | P-101 | Stapler everyone borrows | Office | 4.95 |
| 3 | P-102 | A5 notebook | Stationery | 2.30 |
| 4 | P-103 | Markers (12 colors) | Stationery | 6.80 |
| 5 | P-104 | Office chair | Furniture | 89.00 |
| 6 | P-105 | Desk lamp | Furniture | 24.50 |
You want the price of P-104. First, each function on its own:
=MATCH("P-104",A2:A6,0)returns 4: P-104 is the fourth code in the list.=INDEX(D2:D6,4)returns 89: the fourth price.- Together, with MATCH where the 4 was:
=INDEX(D2:D6,MATCH("P-104",A2:A6,0))returns 89.
Both columns have to start and end in the same row (A2:A6 and D2:D6) so that position 4 is the same in both.
How do you look to the left?
VLOOKUP cannot: the lookup value has to be in the first column. With INDEX and MATCH it does not matter where each column is. For the code of the office chair:
=INDEX(A2:A6,MATCH("Office chair",B2:B6,0)) returns P-104.
How do you cross a row and a column (two-way lookup)?
Add a second MATCH for the column. With the units sold per month:
| K | L | M | N | |
|---|---|---|---|---|
| 1 | Jan | Feb | Mar | |
| 2 | P-101 | 30 | 42 | 25 |
| 3 | P-104 | 3 | 5 | 2 |
=INDEX(L2:N3,MATCH("P-104",K2:K3,0),MATCH("Feb",L1:N1,0)) returns 5: the row of P-104 and the column of February.
INDEX and MATCH, VLOOKUP or XLOOKUP?
| INDEX and MATCH | VLOOKUP | XLOOKUP | |
|---|---|---|---|
| Versions | All | All | Microsoft 365, Excel 2021 and later |
| Look to the left | Yes | No | Yes |
| Two-way lookup (row and column) | Yes | No | Yes, nesting two XLOOKUPs |
| If you insert a column | Keeps working | May bring back another value | Keeps working |
| Functions you type | 2 | 1 | 1 |
If you have Microsoft 365, XLOOKUP is shorter. If the file will open in an older Excel or the test does not let you choose the version, INDEX and MATCH work in all of them.
How does it show up in an Excel test?
In a job interview it is the question that separates basic from intermediate: "Bring in the code from the product name." You cannot do it with VLOOKUP; they expect =INDEX($A$2:$A$6,MATCH(B10,$B$2:$B$6,0)) or XLOOKUP.
In a skills test:
What does
=MATCH("P-104",A2:A6,0)return if A2:A6 holds P-101, P-102, P-103, P-104 and P-105? a) P-104 · b) 4 · c) 5 · d) A5
Answer: b. MATCH returns the position inside the range, not the value or the cell address. To show a friendly message when the code does not exist, wrap it in IFERROR.
Practice INDEX and MATCH
The English exercises are on the way. Until then, copy the catalog into a blank sheet and write the three lookups above: the price, the code from the name, and the units for P-104 in February. Each one uses INDEX and MATCH in a slightly different way.
Available in: all versions of Excel. Sources: Microsoft: INDEX function and MATCH function. Results calculated with Formulo's engine.
Common mistakes
- #N/A: MATCH does not find the value
- =MATCH("P-999",A2:A6,0) gives #N/A because P-999 is not there. Check the code, extra spaces and numbers stored as text. To show a message, wrap the formula in IFERROR.
- #REF!: the position is larger than the range
- D2:D6 has 5 rows. =INDEX(D2:D6,6) asks for the sixth and gives #REF!. It happens when MATCH looks in a longer range than INDEX does.
- It brings back a value from another row
- The two ranges do not start in the same row (A2:A6 with D1:D5) or the 0 is missing in MATCH. Use ranges of the same height that start in the same row, and always type the 0.
- MATCH without 0 returns a position that looks fine
- =MATCH("P-104",A2:A6) returns 4 with this data because the list is sorted. With an unsorted list, the approximate lookup can return another position without warning.
Frequently asked questions
What are INDEX and MATCH called in Spanish?
INDICE and COINCIDIR. In a Spanish Excel: =INDICE(D2:D6;COINCIDIR("P-104";A2:A6;0)).
Why use INDEX and MATCH if VLOOKUP exists?
Because they look to the left, they do not break if you insert columns, and they allow two-way lookups. And, unlike XLOOKUP, they work in every version of Excel.
When do I use XLOOKUP instead of INDEX and MATCH?
If you have Microsoft 365 or Excel 2021 and later, XLOOKUP does the same with one function. INDEX and MATCH are still useful for the two-way lookup and for files that will open in an older Excel.
What does the 0 in MATCH mean?
Exact match. Without the 0, MATCH does an approximate lookup that can return another position if the list is not sorted.
Can I use INDEX and MATCH with a table on another sheet?
Yes. Write the sheet name before each range, in both functions, and lock the ranges with $ so they do not move when you drag the formula.
Related functions
Practice in a real spreadsheet
Exercises with real data and instant checking. The first three are free.