The MATCH function in Excel
Last updated
MATCH returns the position of a value in a range: =MATCH(E2,A2:A9,0) returns 5 if the value in E2 is the 5th cell of A2:A9. It returns a number, not the value itself, so you usually use it inside another formula, such as INDEX. The 0 asks for an exact match.
MATCH syntax
=MATCH(lookup_value, lookup_array, [match_type])
- lookup_value
- The value to find, such as an order number, a name or a weight. It can be a cell, a typed value or another formula, like
MAX(B2:G2). - lookup_array
- A single column or a single row to search, e.g.
A2:A9orB1:G1. Position 1 is the first cell of this range, not row 1 of the sheet. - match_type
0for an exact match.1(the default if you leave it out) returns the largest value less than or equal to lookup_value and needs the range sorted in ascending order.-1returns the smallest value greater than or equal to it and needs a descending sort.
Practice MATCH here
Type your formula straight into the sheet. It’s checked like real Excel, and you can write it with commas or semicolons.
Find an order’s place in the queue
The print queue in A1:C9 is listed in the order the jobs will run. In F2, write a MATCH that returns the position of the order number in E2 within A2:A9.
| Order no. | Customer | Amount ($) | Order no. | Position | |
| 1042 | Bluebird Café | 240.00 | 1048 | ||
| 1057 | Harbor Books | 86.50 | |||
| 1039 | Maple Dental | 1,320.00 | |||
| 1061 | Summit Gym | 415.00 | |||
| 1048 | Pine Street Bakery | 58.00 | |||
| 1053 | Copper Kettle Deli | 730.00 | |||
| 1066 | Riverside Garage | 192.40 | |||
| 1044 | Oak & Ivy Florist | 365.00 |
How to use MATCH, step by step
- Click the cell where you want the position and type
=MATCH(. - Click the cell with the value to find, then type a comma.
- Select the column or row to search, starting on its first value (not the header), then type a comma.
- Type
0)for an exact match, or1)for a band table sorted in ascending order, and press Enter.
MATCH examples
Position of an order in a list
=MATCH(E2,A2:A9,0)
Returns where the order number in E2 sits in A2:A9: 1 for the first cell, 2 for the second, and so on.
Rate band for a weight
=MATCH(D2,$A$2:$A$7,1)
A2:A7 holds the weight each band starts at, sorted ascending. A 7.5 kg parcel lands in the band that starts at 5 kg.
Month with the highest sales
=MATCH(MAX(B2:G2),B2:G2,0)
MAX finds the highest value and MATCH finds its position in the row, which is the month number.
Check whether a value is in a list
=ISNUMBER(MATCH(E2,$A$2:$A$9,0))
Returns TRUE if E2 appears in the list and FALSE if MATCH returns #N/A.
Common MATCH errors and how to fix them
| Result | Cause | Fix |
|---|---|---|
| #N/A | With 0, the value isn’t in the range: a typo, an extra space or a number stored as text. With 1, the value is smaller than the first one in the range. | Check the value and clean the data with TRIM. For a band table, start the first band at the lowest possible value, such as 0. |
| Wrong position, no error | The match type is missing, so MATCH uses approximate match (1) on a column that isn’t sorted. | End MATCH with 0 for an exact match: MATCH(E2,A2:A9,0). |
| Position off by one | The range starts on the header, e.g. A1:A9, so every position counts one cell too many. | Start the range on the first value: A2:A9. |
| Wrong band | The band table isn’t sorted in ascending order, or the range moved when you copied the formula. | Sort the first column from smallest to largest and lock the range with $. |
MATCH vs INDEX + MATCH and XLOOKUP
MATCH only returns a position. To return a value from another column, put it inside INDEX: =INDEX(C2:C9,MATCH(E2,A2:A9,0)), which works in every Excel version and is the next lesson. XLOOKUP (Excel 2021 and Microsoft 365) returns the value in one function, and XMATCH is its counterpart for positions, with exact match as the default. MATCH remains the one that opens in any version.
Frequently asked questions
What does MATCH return?
A number: the position of the value in the range, counted from the first cell of the range. It doesn’t return the value itself.
What do 0, 1 and -1 mean in MATCH?
0 is an exact match. 1 returns the largest value that is less than or equal to the lookup value (sorted ascending). -1 returns the smallest value that is greater than or equal to it (sorted descending).
Is MATCH case-sensitive?
No. MATCH treats “boston” and “Boston” as the same value.
Can MATCH search a row?
Yes. Give it a single row, such as B1:G1, and it returns the column position within that row.
Keep practising
All 5 MATCH exercises, with explanations and progress tracking.
Open the MATCH practice set