MATCH returns the relative position of a lookup value within a one-dimensional range or array. It tells you where a value sits (for example, 3rd item) rather than returning the value itself.
=MATCH(lookup_value, lookup_array, [match_type])What does MATCH do?
The MATCH function answers a simple but powerful question: where is this value in my list? You give it something to find and a single row or column to search, and it returns a number representing the position. For example, if you search for "North" in a list of regions, MATCH might return 2, meaning North is the second item.
Why does position matter? Because many Excel tasks depend on knowing where data lives. You might need to pull a value from a parallel column, highlight a row, or build a dynamic reference. MATCH is often paired with INDEX to create flexible lookups that don't break when columns are inserted. It's also a building block for advanced formulas like dynamic ranges and conditional formatting rules.
Imagine you have a monthly sales report with product names in column A and sales figures in column B. You want to quickly find the sales figure for a specific product. Instead of scrolling, you can use MATCH to find the product's row position and then INDEX to retrieve the sales amount. This is faster and less error-prone, especially with large datasets.
When to use MATCH
- Finding the row number of a specific item in a list
- Building dynamic INDEX/MATCH lookups that are resistant to column changes
- Locating the position of a value for use in other formulas (e.g., OFFSET, CHOOSE)
- Checking if a value exists in a list and getting its rank
- Creating interactive dashboards where users select from a dropdown and see related data
MATCH syntax
=MATCH(lookup_value, lookup_array, [match_type])| Argument | Required? | What to enter |
|---|---|---|
lookup_value | Required | The value you want to find. It can be a number, text, logical value, or a cell reference. Example: "North" or 1200. |
lookup_array | Required | A single row or column range to search. It must be one-dimensional. Example: A2:A10. |
match_type | Optional | Optional. Use 0 for exact match, 1 for less than (default), or -1 for greater than. If omitted, 1 is assumed. Example: 0. |
What it returns: A number representing the position of lookup_value within lookup_array. If no match is found, returns #N/A.
MATCH examples
1 Find the position of a product in a list
You have a list of products in column A and want to know the position of "Monitor". The formula =MATCH("Monitor", A2:A6, 0) searches A2:A6 for an exact match. It finds "Monitor" in the third cell (A4), so it returns 3. This tells you that Monitor is the third item in the list.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Product | |||
| 2 | Keyboard | 3 | ||
| 3 | Mouse | |||
| 4 | Monitor | |||
| 5 | Printer | |||
| 6 | Scanner | |||
| 7 |
2 Find a salesperson's position by name
Column A lists salespeople. You want the position of "Emma" in the list. =MATCH("Emma", A2:A5, 0) searches A2:A5 for an exact match. Emma is in A4, which is the third cell in the range, so the result is 3. This position can then be used to retrieve Emma's sales from another column.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Salesperson | |||
| 2 | John | 3 | ||
| 3 | Sarah | |||
| 4 | Emma | |||
| 5 | Mike | |||
| 6 |
3 Use MATCH with a cell reference for dynamic lookup
Instead of hardcoding the lookup value, you can refer to a cell. Here, D2 contains "Q3" and you want its position in the list of quarters in A2:A5. =MATCH(D2, A2:A5, 0) searches for the value in D2. Q3 is the third item, so it returns 3. This makes the formula reusable when D2 changes.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Quarter | ||||
| 2 | Q1 | Q3 | |||
| 3 | Q2 | ||||
| 4 | Q3 | 3 | |||
| 5 | Q4 | ||||
| 6 |
4 Find approximate match for a grade threshold
MATCH with match_type 1 finds the largest value less than or equal to the lookup value, assuming the list is sorted ascending. Here, scores in A2:A5 are sorted: 60, 70, 80, 90. The formula =MATCH(75, A2:A5, 1) looks for 75. It finds 70 (position 2) as the largest value ≤75, so it returns 2. This is useful for grading scales.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Score | |||
| 2 | 60 | 2 | ||
| 3 | 70 | |||
| 4 | 80 | |||
| 5 | 90 | |||
| 6 |
5 Combine MATCH with INDEX to retrieve a value
You want the sales amount for "Product C". Products are in A2:A4, sales in B2:B4. First, MATCH finds the position of "Product C" in A2:A4: =MATCH("Product C", A2:A4, 0) returns 3. Then INDEX uses that position to get the third value from B2:B4, which is 1500. The full formula is =INDEX(B2:B4, MATCH("Product C", A2:A4, 0)).
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Product | Sales | |||
| 2 | Product A | 1000 | 1500 | ||
| 3 | Product B | 1200 | |||
| 4 | Product C | 1500 | |||
| 5 |
Common MATCH errors and how to fix them
| Error | Why it happens | How to fix it |
|---|---|---|
| #N/A | The lookup value is not found in the lookup array. | Check for typos, extra spaces, or mismatched data types. Use TRIM or EXACT to compare, or wrap MATCH in IFERROR to handle missing values gracefully. |
| #VALUE! | The lookup_array is not a one-dimensional range (e.g., a two-dimensional range or an array with multiple rows and columns). | Ensure lookup_array is a single row or column. For example, use A2:A10 or B1:Z1, not A1:C10. |
| #N/A (with match_type 1 or -1) | The lookup_array is not sorted correctly for approximate match. For match_type 1, it must be ascending; for -1, descending. | Sort the lookup_array accordingly, or use match_type 0 for exact match if sorting is not possible. |
| Wrong result (off by one) | The lookup_array does not start at the first row of the list, so the returned position is relative to the range, not the sheet. | Remember that MATCH returns the position within lookup_array. If you need the actual row number, add the starting row minus 1, or use ROW() with INDEX. |
Tips for using MATCH
- Always use match_type 0 for exact matches unless you specifically need approximate matching.
- When using approximate match, ensure the lookup_array is sorted in ascending order (for match_type 1) or descending (for -1).
- Combine MATCH with INDEX for flexible lookups that don't break when columns are inserted or deleted.
- Use MATCH to check if a value exists: =ISNUMBER(MATCH(value, range, 0)) returns TRUE if found.
- For case-sensitive matching, use EXACT inside an array formula or XMATCH (if available).
- Remember that MATCH returns the position within the range, not the row number on the sheet.
Frequently asked questions
What is the difference between MATCH and VLOOKUP?
MATCH returns the position of a value in a list, while VLOOKUP returns a value from a specified column. MATCH is often used with INDEX to create more flexible lookups.
Can MATCH search from right to left?
No, MATCH only searches from top to bottom (or left to right) within the lookup_array. To search right to left, use INDEX/MATCH with a reversed range or XLOOKUP if available.
Why does MATCH return #N/A when the value exists?
Common causes include extra spaces, numbers stored as text, or case differences. Use TRIM and check data types. Also, ensure match_type is 0 for exact match.
What does match_type 1 do?
It finds the largest value less than or equal to the lookup value, assuming the array is sorted ascending. It's useful for finding thresholds like tax brackets or grades.
Can MATCH work with wildcards?
Yes, when match_type is 0, you can use wildcards like * and ? in the lookup_value. For example, =MATCH("A*", A2:A10, 0) finds the first value starting with A.
Related functions
Every example on this page was recalculated by SheetSkill’s spreadsheet engine. MATCH reference