Excel INDEX and MATCH
INDEX and MATCH
INDEX and MATCH means using two functions together to look up a value in a table. It works like VLOOKUP, but it can look in any direction.
MATCH finds the position of the search value. INDEX returns the value at that position from another column.
The combined formula looks like this:
=INDEX(return_range, MATCH(lookup_value, lookup_range, 0))
These names are only labels to explain the formula:
Return_range: The column with the value you want to get back.
Lookup_value: The value to search for. This is usually the cell where the search value is entered.
Lookup_range: The column to search in.
0: Exact match. Always type it. Without it, MATCH uses approximate match.
Note: The lookup_range and the return_range must start and end on the same rows, like B2:B21 and E2:E21. Otherwise the position from MATCH points to the wrong row.
Note: The different parts of the function are separated by a symbol, like comma , or semicolon ;
The symbol depends on your Language Settings.
How to use INDEX and MATCH:
- Select a cell (
H4) - Type
=INDEX - Double click the INDEX command
- Mark the range to return a value from (
E2:E21) - Type (
,) - Type
MATCH - Double click the MATCH command
- Select the cell where the search value will be entered (
H3) - Type (
,) - Mark the range to search in (
B2:B21) - Type (
,) - Type
0for an exact match - Type two closing parentheses (
))) - Hit enter
- Enter a value in the search cell (
H3)
Let's have a look at an example!
This is the same Pokemon table as on the VLOOKUP page. Copy it and paste it into cell A1 to follow along:
Example data
A B C D E 1 ID# Name Type 1 Type 2 Total 2 1 Bulbasaur Grass Poison 318 3 2 Ivysaur Grass Poison 405 4 3 Venusaur Grass Poison 525 5 4 Charmander Fire 309 6 5 Charmeleon Fire 405 7 6 Charizard Fire Flying 534 8 7 Squirtle Water 314 9 8 Wartortle Water 405 10 9 Blastoise Water 530 11 10 Caterpie Bug 195 12 11 Metapod Bug 205 13 12 Butterfree Bug Flying 395 14 13 Weedle Bug Poison 195 15 14 Kakuna Bug Poison 205 16 15 Beedrill Bug Poison 395 17 16 Pidgey Normal Flying 251 18 17 Pidgeotto Normal Flying 349 19 18 Pidgeot Normal Flying 479 20 19 Rattata Normal 253 21 20 Raticate Normal 413
Type the labels Search Name in G3 and Total in G4.
Use INDEX and MATCH to find the Total of a Pokemon based on its Name. Type Charizard in H3:
| A | B | C | D | E | F | G | H | |
|---|---|---|---|---|---|---|---|---|
| 1 | ID# | Name | Type 1 | Type 2 | Total | |||
| 2 | 1 | Bulbasaur | Grass | Poison | 318 | |||
| 3 | 2 | Ivysaur | Grass | Poison | 405 | Search Name | Charizard | |
| 4 | 3 | Venusaur | Grass | Poison | 525 | Total | 534 | |
| 5 | 4 | Charmander | Fire | 309 | ||||
| 6 | 5 | Charmeleon | Fire | 405 | ||||
| 7 | 6 | Charizard | Fire | Flying | 534 | |||
| 8 | 7 | Squirtle | Water | 314 | ||||
| 9 | 8 | Wartortle | Water | 405 | ||||
| 10 | 9 | Blastoise | Water | 530 | ||||
| 11 | 10 | Caterpie | Bug | 195 | ||||
| 12 | 11 | Metapod | Bug | 205 | ||||
| 13 | 12 | Butterfree | Bug | Flying | 395 | |||
| 14 | 13 | Weedle | Bug | Poison | 195 | |||
| 15 | 14 | Kakuna | Bug | Poison | 205 | |||
| 16 | 15 | Beedrill | Bug | Poison | 395 | |||
| 17 | 16 | Pidgey | Normal | Flying | 251 | |||
| 18 | 17 | Pidgeotto | Normal | Flying | 349 | |||
| 19 | 18 | Pidgeot | Normal | Flying | 479 | |||
| 20 | 19 | Rattata | Normal | 253 | ||||
| 21 | 20 | Raticate | Normal | 413 |
=INDEX(E2:E21, MATCH(H3, B2:B21, 0))
Excel works from the inside out:
MATCH(H3, B2:B21, 0)finds Charizard at position 6 inB2:B21.INDEX(E2:E21, 6)returns the 6th value inE2:E21, which is534.
Look to the Left
VLOOKUP can only return values from columns to the right of the search column. With INDEX and MATCH, the return column can be anywhere, also to the left.
Find the ID# of a Pokemon based on its Name. Change the label in G4 to ID#, and type Butterfree in H3:
| G | H | |
|---|---|---|
| 3 | Search Name | Butterfree |
| 4 | ID# | 12 |
=INDEX(A2:A21, MATCH(H3, B2:B21, 0))
MATCH finds Butterfree in B13, and INDEX returns the value from the same row in A2:A21, which is 12.
Two-Way Lookup
INDEX can take both a row number and a column number. Use one MATCH for each, and you can pick the column by its header name.
Change the label in G4 to Search Column, and type Result in G5. Type Pidgeot in H3, Total in H4, and the formula in H5:
| G | H | |
|---|---|---|
| 3 | Search Name | Pidgeot |
| 4 | Search Column | Total |
| 5 | Result | 479 |
=INDEX(A2:E21, MATCH(H3, B2:B21, 0), MATCH(H4, A1:E1, 0))
MATCH(H3, B2:B21, 0)finds Pidgeot at position 18.MATCH(H4, A1:E1, 0)finds Total at position 5.INDEX(A2:E21, 18, 5)returns the value in row 18, column 5 of the table, which is479.
Change H4 to Type 1, and the result changes to Normal.
Note: The ranges must line up. B2:B21 starts on the same row as A2:E21, and A1:E1 starts in the same column. That way the positions from MATCH point to the right row and column.
Why Use INDEX and MATCH?
- It works in every version of Excel. XLOOKUP is not available in Excel 2019 or earlier.
- It can look to the left.
- It uses ranges, not column numbers, so inserting a column in the table does not break it.
- It can do two-way lookups.
If you have Excel for Microsoft 365, Excel 2021 or Excel 2024, XLOOKUP does the same with a shorter formula.
VLOOKUP vs INDEX and MATCH vs XLOOKUP
| VLOOKUP | INDEX and MATCH | XLOOKUP | |
|---|---|---|---|
| Excel versions | All versions | All versions | Microsoft 365, Excel 2021, Excel 2024 and Excel for the web |
| Look to the left | No | Yes | Yes |
| Exact match | Type FALSE or 0 | Type 0 in MATCH | The default |
| The return column is set by | A column number | A range | A range |
| Inserting a column in the table | Can break the result | Adjusts automatically | Adjusts automatically |
| Own message when nothing is found | No | No | Yes, with if_not_found |
| Functions in the formula | One | Two | One |
