What it does
MATCH returns the position of a value, not the value itself. If West is the second cell of A2:A6, =MATCH("West",A2:A6,0) returns 2. On its own that answers "is it in the list?"; combined with INDEX it pulls a value from another column.
The third argument matters, and its default is not exact match. 0 looks for an identical value. 1 (or omitted) returns the position of the last value that is less than or equal to the lookup value in a list sorted from smallest to largest; -1 finds the last value that is greater than or equal in a list sorted from largest to smallest. When looking up codes or names, always use 0.
In Microsoft 365 and Excel 2021, XMATCH does the same job with exact match as the default and an option to search from the end. To get the value directly, XLOOKUP or VLOOKUP is shorter.
Syntax
=MATCH(lookup_value, lookup_array, [match_type])=KAÇINCI(aranan; arama_dizisi; [eşleştirme_türü])Arguments in square brackets are optional. English Excel separates arguments with commas (,) and uses a dot for decimals; Turkish Excel uses semicolons (;) and a decimal comma. Excel shows function names in its own language when it opens the file.
Arguments
| Argument | Required | Description |
|---|---|---|
lookup_value | Required | The value whose position you want, or the cell that holds it. With match type 0, text lookups can use the * and ? wildcards. |
lookup_array | Required | The single column or row to search, e.g. $E$2:$E$5. |
match_type | Optional | 0: exact match. 1 or omitted: the last value less than or equal to the lookup value in an ascending list. -1: the last value greater than or equal to it in a descending list. |
Examples
1Find which band a score falls into
The lower bounds of the bands are in column E, sorted from smallest to largest. With type 1, MATCH returns the position of the largest bound that is less than or equal to the score; that position is the band number.
=MATCH(B2,$E$2:$E$5,1)| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Student | Score | Band | From | Band Name | |
| 2 | Mia | 47 | 1 | 0 | Needs work | |
| 3 | Lucas | 68 | 2 | 50 | Fair | |
| 4 | Ella | 85 | 4 | 70 | Good | |
| 5 | Jack | 70 | 3 | 85 | Excellent |
- C2 (filled down to C5)
=MATCH(B2,$E$2:$E$5,1)
Lucas's 68 is between 50 and 70, so the result is 2. Jack, with exactly 70, is in band 3 and Ella, with 85, in band 4. To turn the number into a band name, use INDEX: =INDEX($F$2:$F$5,C2).
We recalculated this example in real Excel (Microsoft 365, 16.0) and compared the results: they match.
2Compare two lists
The customer list is in A and the campaign list in D. If MATCH finds the customer in the campaign list it returns a position, otherwise #N/A; ISNUMBER turns that into TRUE or FALSE, and IF into "Yes" or "No".
=IF(ISNUMBER(MATCH(A2,$D$2:$D$4,0)),"Yes","No")| A | B | C | D | |
|---|---|---|---|---|
| 1 | Customer | In Campaign? | Campaign List | |
| 2 | North Market | Yes | Atlas Ltd | |
| 3 | Atlas Ltd | Yes | Blue Electric | |
| 4 | West Build | No | North Market | |
| 5 | Blue Electric | Yes | ||
| 6 | Sea Foods | No |
- B2 (filled down to B6)
=IF(ISNUMBER(MATCH(A2,$D$2:$D$4,0)),"Yes","No")
North Market is on the campaign list, so B2 shows Yes; West Build is not, so B4 shows No. When you add a name to the campaign list, extend the $D$2:$D$4 range too.
We recalculated this example in real Excel (Microsoft 365, 16.0) and compared the results: they match.
3Total the month chosen by its header
The months are the headers in B1:E1. MATCH finds the position of the month in G2 in the header row; INDEX with row_num 0 returns that whole column and SUM adds it up.
=SUM(INDEX($B$2:$E$4,0,MATCH(G2,$B$1:$E$1,0)))| A | B | C | D | E | F | G | H | |
|---|---|---|---|---|---|---|---|---|
| 1 | Branch | Jan | Feb | Mar | Apr | Month | Total | |
| 2 | Camden | 412,000 | 398,500 | 436,200 | 421,900 | Mar | 1,124,850 | |
| 3 | Leeds | 356,800 | 371,200 | 365,900 | 380,400 | |||
| 4 | Bristol | 298,400 | 305,100 | 322,750 | 317,600 |
Mar is the third column of the header row, so the March sales of all three branches are added: 1,124,850. Type Apr in G2 and the total switches without touching the formula.
We recalculated this example in real Excel (Microsoft 365, 16.0) and compared the results: they match.
Common errors and fixes
- #N/A
Cause: With exact match the value is not in the list; with type 1 the lookup value is smaller than the smallest value. Sometimes one list stores the code as a number and the other as text.
Fix: Check spelling, spaces and number-versus-text; use IFERROR to show a message.
- Wrong result
Cause: The third argument was left empty, so Excel used approximate match on an unsorted list and returned another position.
Fix: When looking up codes and names, always pass 0 as the match type.
Good to know
- The result is a position inside the range; if the range starts on row 2, the sheet row is one more than the result.
- To test whether a value appears in another list, use
=ISNUMBER(MATCH(A2,$D$2:$D$4,0)): TRUE if found, FALSE if not.
In Nexell
Get Nexell to do it from a sentence
Mark each customer in my customer list with "Yes" if they are on the campaign list and "No" if not
Convert to tableBring both lists into the tool and type this. Nexell builds the comparison with real Excel formulas and the preview shows how many customers matched; in the downloaded file, the marks update when either list changes.
Nexell uses AI to understand the request and builds the result with real Excel formulas; the formula may differ from the example on this page. You see a preview first and nothing changes until you press Apply; formulas stay live in the downloaded .xlsx file.
Free account · no card needed · 30 credits a month
Frequently asked questions
How do I use MATCH with INDEX?
MATCH finds the position of the lookup value and INDEX returns the value at that position from another column: =INDEX(C2:C9,MATCH(F2,A2:A9,0)) looks for F2 in column A and returns the value from column C on the same row.
Why does MATCH return the wrong position?
Most likely the third argument is empty. Without it, MATCH assumes the list is sorted and uses approximate match. Add 0 at the end for exact match: =MATCH(F2,A2:A9,0).
Related functions
- INDEXİNDİS · Returns the value at a given row and column of a range.
- XMATCHÇAPRAZEŞLEŞTİR · Position of a value; exact match by default.
- XLOOKUPÇAPRAZARA · Searches an array and returns the matching item; the fourth argument if not found.
- VLOOKUPDÜŞEYARA · Looks up a value in the first column and returns a value from the same row. Use 0 as the last argument for exact match.
- ISNUMBERESAYIYSA · TRUE if the value is a number.