Lookup
MATCH function in Excel:syntax and examples
MATCH returns the position of a value inside a row or column. In Spanish Excel it is called COINCIDIR. Syntax: =MATCH(lookup_value, lookup_array, [match_type]).
Syntax
=MATCH(lookup_value, lookup_array, [match_type])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 |
|---|---|
lookup_value | Required. What you are looking for. Example: "P-104" or a cell. |
lookup_array | Required. A single column or a single row. Example: A2:A6. |
match_type | Optional, but always type it. 0: exact. 1: the largest value that does not go over the lookup value, with the list in ascending order. -1: the smallest value that is not below the lookup value, with the list in descending order. If you leave it out, Excel uses 1. |
How do you use MATCH? A step-by-step example
MATCH answers "which spot in the list is this?" In a stationery catalog you want to know which position the code P-104 is in:
| 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 |
=MATCH("P-104",A2:A6,0) returns 4.
"P-104"is what you look for.A2:A6is the list. The position is counted from its first cell, so the 4 matches row 5 of the sheet.0asks for the exact value.
It works the same in a row: with the months Jan, Feb and Mar in L1:N1, =MATCH("Feb",L1:N1,0) returns 2.
Which match type do you use: 0, 1 or -1?
0 for almost everything. 1 and -1 are for bands, like a commission table by sales.
| Type | Looks for | The list has to be | Example | Result |
|---|---|---|---|---|
| 0 | The exact value | In any order | =MATCH("P-104",A2:A6,0) | 4 |
| 1 (or empty) | The largest value that does not go over the lookup value | Smallest to largest | =MATCH(7200,H2:H5,1) | 3 |
| -1 | The smallest value that is not below the lookup value | Largest to smallest | =MATCH(7200,P2:P5,-1) | 1 |
The band lists in the examples are H2:H5 (0, 1000, 5000, 10000) and P2:P5 (10000, 5000, 1000, 0). With type 1, 7200 falls in the 5000 band, which is the third. With type 1 and a value smaller than the first one, =MATCH(-5,H2:H5,1) gives #N/A.
What is MATCH for if it returns a number?
On its own, for two things: knowing the position and knowing whether something exists.
| You want | Formula | Result |
|---|---|---|
| Which position holds the highest price | =MATCH(MAX(D2:D6),D2:D6,0) | 4 |
| Whether a code exists | =ISNUMBER(MATCH("P-104",A2:A6,0)) | TRUE |
| Whether a code exists (it is not there) | =ISNUMBER(MATCH("P-999",A2:A6,0)) | FALSE |
And its main use: handing the position to INDEX to bring back the value. That complete lookup is explained in INDEX and MATCH.
MATCH or XMATCH?
| MATCH | XMATCH | |
|---|---|---|
| Versions | All | Microsoft 365, Excel 2021 and later |
| Default match | Approximate (1) | Exact |
| Next larger or smaller | Needs the list sorted | No order needed |
| Search from the end | No | Yes |
If you have Microsoft 365, XMATCH avoids the mistake of forgetting the 0. If the file will open in an older Excel, MATCH.
How does MATCH show up in an Excel test?
In a job interview it is usually "Which place did this runner finish in?" or half of a lookup with INDEX. What they look for: that you type the 0.
In a skills test:
You want the exact position of a code in a list that is not sorted. What do you put in match_type? a) 1 · b) 0 · c) -1 · d) Nothing, you leave it empty
Answer: b. With 1, -1 or empty, MATCH assumes the list is sorted and may return another position. For a lookup that returns the value itself, see VLOOKUP and XLOOKUP.
Practice MATCH
The English exercises are on the way. Until then, copy the catalog into a blank sheet, find the position of each code, and then try the commission list with types 1 and -1 to see how the position changes.
Available in: MATCH, in all versions of Excel. XMATCH, in Microsoft 365 and Excel 2021 and later. Sources: Microsoft: MATCH function and XMATCH function. Results calculated with Formulo's engine.
Common mistakes
- #N/A: the value is not there
- =MATCH("P-999",A2:A6,0) gives #N/A. Check for extra spaces and numbers stored as text. If you only want to know whether it exists, use ISNUMBER (on this page).
- #N/A with a number that is in the bands table
- With 0, MATCH only accepts the exact value. =MATCH(7200,H2:H5,0) gives #N/A because 7200 is not in the list. For bands, use 1 with the list sorted from smallest to largest.
- It returns a wrong position without warning
- The 0 is missing and the list is not sorted. Without the third argument, MATCH does an approximate lookup that assumes the list is in ascending order.
- You expected the value and you get a number
- MATCH returns the position: 4, not P-104. To bring back the value at that position, put MATCH inside INDEX.
Frequently asked questions
What is MATCH called in Spanish?
COINCIDIR. In a Spanish Excel: =COINCIDIR("P-104";A2:A6;0).
What does the 0 in MATCH mean?
Exact match: only the same value counts. Always type it unless you are looking up bands.
How do I know whether a value is in a list?
With ISNUMBER and MATCH: =ISNUMBER(MATCH("P-104",A2:A6,0)) returns TRUE if it is there and FALSE if not. COUNTIF greater than 0 also works.
Is MATCH case-sensitive?
No. =MATCH("p-104",A2:A6,0) returns 4, the same as with P-104.
What is the difference between MATCH and XMATCH?
XMATCH is exact by default, does not need the list sorted to find the next larger or smaller, and can search from the end. It is in Microsoft 365 and Excel 2021 and later: =XMATCH("P-104",A2:A6) returns 4.
Can I use wildcards in MATCH?
Yes, with type 0: =MATCH("Office*",B2:B6,0) returns 4, the position of the first product that starts with Office.
Related functions
Practice in a real spreadsheet
Exercises with real data and instant checking. The first three are free.