Lookup function
Excel MATCH function
MATCH searches a single row or column and returns the position of a value, not the value itself. On its own it tells you where something is. Paired with INDEX it becomes a lookup that can go in any direction.
=MATCH(lookup_value, lookup_array, [match_type])Arguments
lookup_value- The value to find.
lookup_array- The single row or column to search.
match_type- 0 for an exact match. 1 (the default) finds the largest value less than or equal to it in ascending data. -1 finds the smallest value greater than or equal to it in descending data.
MATCH examples
Every result below was calculated live from this sample sheet.
| A | B | C | |
|---|---|---|---|
| 1 | Shipping tier | Min weight (kg) | Rate |
| 2 | Letter | 0 | 4.5 |
| 3 | Small | 1 | 8.95 |
| 4 | Medium | 5 | 14.25 |
| 5 | Large | 20 | 29 |
| 6 | Freight | 50 | 75 |
Result: 3
Medium is the 3rd item in the list, so MATCH returns 3.
Result: 3
A 12 kg parcel falls in the band that starts at 5 kg, position 3.
Result: 14.25
INDEX uses that position to return the rate: 14.25.
Result: #N/A
No exact match, so MATCH returns #N/A.
How to use MATCH
- Type =MATCH( and the value to find.
- Select a single row or column to search.
- Type 0 for an exact match and close the bracket.
- Use the result inside INDEX to return a value.
Common MATCH mistakes
Leaving out match_type
The default is 1, an approximate match that assumes sorted data. Type 0 for an exact match.
Selecting more than one column
lookup_array must be one row or one column. A2:C6 gives #N/A.
Expecting the value, not the position
MATCH returns a number like 3. Wrap it in INDEX to get the value in that position.
MATCH FAQ
How do I use INDEX and MATCH together in Excel?
MATCH finds the row number and INDEX returns the value: =INDEX(C2:C10,MATCH("Bob",A2:A10,0)). It works even when the return column is to the left.
Why does MATCH return #N/A?
There is no match, or you used the default match_type on unsorted data. Add 0 for an exact match and check for extra spaces.
Is MATCH case sensitive?
No. MATCH treats "apple" and "APPLE" as the same. Use EXACT inside an array formula if case matters.
Practice MATCH in these lessons
Guides that use MATCH
- How to use INDEX MATCH in Excel
- XLOOKUP with multiple criteria
- How to compare two columns in Excel
- VLOOKUP vs XLOOKUP
- #REF! error in Excel
- Excel formulas cheat sheet: the 25 everyone should know
Related functions
Updated 2026-10-31