Lookup & Reference
All versions
◉ 14 views
=MATCH()
How to use the MATCH function in Excel to search for a specified item in a range of cells, returning the relative position of that item in the range.
Applies To:
Excel for Microsoft 365Excel for Microsoft 365 for MacExcel 2024Excel 2024 for MacExcel 2021Excel 2021 for MacExcel 2019Excel 2016
=MATCH(lookup_value, lookup_array, [match_type])
Hover argument names in the table for details.
Output typeNumber
Popularity80 / 100
VolatileNo
Dynamic ArrayNo
📥 Download sample .xlsx — Coming soon
▦ Try in Playground
Dedicated file for this function — safe & lightweight · sample data + live formulas
⬇ Download Sample Workbook
A dedicated .xlsx for =MATCH() with Persian sample data & live formulas — open in Excel and practice.
- Sheet “Sample” — data table + formula in place
- Sheet “Guide” — syntax, arguments & tips from this page
- Instant & secure (token + rate limit)
🧠 🧠 Quiz this function
%d questions — syntax, example & errors — test yourself!
◧ Arguments
| Argument | Type | Required | Description |
|---|---|---|---|
lookup_value |
Any type | Required | The value that you want to match in lookup_array. For example, when you look up someone's number in a telephone book, you are using the person's name as the lookup value, but the telephone number is the value you want. The lookup_value argument can be a value (number, text, or logical value) or a cell reference to a number, text, or logical value. |
lookup_array |
Range | Required | The range of cells being searched. |
match_type |
Number | Optional | The number -1, 0, or 1. The match_type argument specifies how Excel matches lookup_value with values in lookup_array. The default value for this argument is 1. The following table describes how the function finds values based on the setting of the match_type argument. 1 or omitted = MATCH finds the largest value that is less than or equal to lookup_value. The values in the lookup_array argument must be placed in ascending order, for example: ...-2, -1, 0, 1, 2, ..., A-Z, FALSE, TRUE.; 0 = MATCH finds the first value that is exactly equal to lookup_value. The values in the lookup_array argument can be in any order.; -1 = MATCH finds the smallest value that is greater than or equal tolookup_value. The values in the lookup_array argument must be placed in descending order, for example: TRUE, FALSE, Z-A, ...2, 1, 0, -1, -2, ..., and so on.. |
▣ Examples
▦ Data
| Cell | Value |
|---|---|
A2 |
Product |
B2 |
Count |
A3 |
Bananas |
B3 |
25 |
A4 |
Oranges |
B4 |
38 |
A5 |
Apples |
B5 |
40 |
A6 |
Pears |
B6 |
41 |
A7 |
Product |
B7 |
Count |
A8 |
Bananas |
B8 |
25 |
A9 |
Oranges |
B9 |
38 |
A10 |
Apples |
B10 |
40 |
A11 |
Pears |
B11 |
41 |
Because there is not an exact match, the position of the next lowest value (38) in the range B2:B5 is returned.
=MATCH(39,B2:B5,1)
Result:
2
The position of the value 41 in the range B2:B5.
=MATCH(41,B2:B5,0)
Result:
4
Returns an error because the values in the range B2:B5 are not in descending order.
=MATCH(40,B2:B5,-1)
Result:
#N/A
✦ Tips
- Try using the new XMATCH function, an improved version of MATCH that works in any direction and returns exact matches by default, making it easier and more convenient to use than its predecessor.
- Use MATCH instead of one of the LOOKUP functions when you need the position of an item in a range instead of the item itself. For example, you might use the MATCH function to provide a value for the row_num argument of the INDEX function.
💬 Comments & Q&A 0 approved
🔒 Your comment will be published after review.Share your thoughts — visible after admin approval
No comments yet — be the first!