Excel MATCH Function
MATCH Function
The MATCH function is a premade function in Excel, which searches for a value in a range and returns its position.
MATCH returns the position, not the value. If the value is in the 7th cell of the range, MATCH returns 7.
It is typed =MATCH and has the following parts:
=MATCH(lookup_value, lookup_array, [match_type])
Note: The different parts of the function are separated by a symbol, like comma , or semicolon ;
The symbol depends on your Language Settings.
Lookup_value: The value to search for. This is usually the cell where the search value is entered.
Lookup_array: The range to search in. It must be a single column or a single row.
Match_type: Optional. How the lookup_value is matched:
| Match_type | Description |
|---|---|
0 | Exact match. The data can be in any order. |
1 | The largest value that is less than or equal to lookup_value. The data must be sorted in ascending order (smallest to largest). This is the default if you leave match_type out. |
-1 | The smallest value that is greater than or equal to lookup_value. The data must be sorted in descending order (largest to smallest). |
Note: Always type 0 for an exact match. If you leave match_type out, Excel uses 1. On data that is not sorted, like the names in the example below, 1 can return a wrong position without any error.
How to use the MATCH function:
- Select a cell (
H4) - 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 - 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 Position in G4.
Use the MATCH function to find the position of a Pokemon in the Name column. Type Squirtle 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 | Squirtle | |
| 4 | 3 | Venusaur | Grass | Poison | 525 | Position | 7 | |
| 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 |
=MATCH(H3, B2:B21, 0)
Squirtle is in B8. That is the 7th cell of B2:B21, so MATCH returns 7, not 8.
You can also type the value straight into the formula. Text must be inside double quotes: =MATCH("Squirtle", B2:B21, 0) also returns 7.
MATCH is Not Case-Sensitive
MATCH does not see a difference between uppercase and lowercase letters. Type squirtle in lowercase in H3:
| G | H | |
|---|---|---|
| 3 | Search Name | squirtle |
| 4 | Position | 7 |
MATCH still returns 7.
When There is No Match
If MATCH does not find the value, it returns #N/A. Pikachu is not in the table:
| G | H | |
|---|---|---|
| 3 | Search Name | Pikachu |
| 4 | Position | #N/A |
MATCH in a Row
The lookup_array can also be a row. Find the position of the Total header in the header row A1:E1.
Change the label in G3 to Search Header, and type Total in H3:
| G | H | |
|---|---|---|
| 3 | Search Header | Total |
| 4 | Position | 5 |
=MATCH(H3, A1:E1, 0)
Total is the 5th cell in A1:E1, so MATCH returns 5.
MATCH With INDEX
A position on its own is often not what you need. Combine MATCH with the INDEX function, which returns the value at a position:
=INDEX(E2:E21, MATCH(H3, B2:B21, 0))
With Squirtle in H3, MATCH returns 7, and INDEX returns the 7th value in E2:E21, which is 314.
This is a lookup that works in every version of Excel. Learn more in the INDEX and MATCH chapter.
