Skip to content

Excel function · Lookup & reference

Excel MATCH function

MATCH tells you the position of a value in a column or row: where a customer sits in a list, which band a score falls into. It is usually paired with INDEX.

In Turkish Excel: KAÇINCI

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

English Excel=MATCH(lookup_value, lookup_array, [match_type])
Turkish Excel=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

ArgumentRequiredDescription
lookup_valueRequiredThe value whose position you want, or the cell that holds it. With match type 0, text lookups can use the * and ? wildcards.
lookup_arrayRequiredThe single column or row to search, e.g. $E$2:$E$5.
match_typeOptional0: 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.

C2fx=MATCH(B2,$E$2:$E$5,1)
Find which band a score falls into: example table
ABCDEF
1StudentScoreBandFromBand Name
2Mia4710Needs work
3Lucas68250Fair
4Ella85470Good
5Jack70385Excellent
  • 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".

B2fx=IF(ISNUMBER(MATCH(A2,$D$2:$D$4,0)),"Yes","No")
Compare two lists: example table
ABCD
1CustomerIn Campaign?Campaign List
2North MarketYesAtlas Ltd
3Atlas LtdYesBlue Electric
4West BuildNoNorth Market
5Blue ElectricYes
6Sea FoodsNo
  • 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.

H2fx=SUM(INDEX($B$2:$E$4,0,MATCH(G2,$B$1:$E$1,0)))
Total the month chosen by its header: example table
ABCDEFGH
1BranchJanFebMarAprMonthTotal
2Camden412,000398,500436,200421,900Mar1,124,850
3Leeds356,800371,200365,900380,400
4Bristol298,400305,100322,750317,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

Type this in Nexell

Mark each customer in my customer list with "Yes" if they are on the campaign list and "No" if not

Convert to table

Bring 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

All lookup & reference functions →

Stop memorising formulas. Describe them.

Bring your table into the tool and say what you need; Nexell builds the formula and shows it in a preview.

Try it free