Position of a name
| A | |
|---|---|
| 1 | Name |
| 2 | Ana |
| 3 | Ben |
| 4 | Chloe |
| 5 | Dev |
Formula: C1: =MATCH("Chloe",A2:A5,0)
Result: 3
Chloe is the third item of A2:A5. The 0 asks for an exact match.
Lookup & reference
MATCH
Position within a range. 0 means exact match.
Syntax
MATCH(lookup_value, lookup_array, [match_type])Example: =MATCH(A2,B2:B100,0)
Available in: All versions
MATCH returns the position of a value inside a single row or column, not the value itself. Use =MATCH(value, range, 0) for an exact match; it is usually paired with INDEX to build lookups.
When to use it
- You need to know where an item sits in a list, for example the row number of an employee.
- You are building an INDEX/MATCH lookup.
- You want a dynamic column number for VLOOKUP, so it picks the column by header name.
- You need to check whether a value appears in a list (wrapped in ISNUMBER).
Arguments explained
- lookup_value. The value whose position you want. It can be a number, text or a cell reference.
- lookup_array. A single row or column to search. MATCH returns a position inside this range, not a worksheet row number.
- match_type. 0 finds the first exact match and is the safe choice. 1 (default) finds the largest value not above the target in ascending data. -1 finds the smallest value not below it in descending data. Forgetting this argument is the most common reason for wrong answers.
Worked examples
Approximate match for grade bands
| A | B | |
|---|---|---|
| 1 | Score from | Grade |
| 2 | 0 | F |
| 3 | 60 | D |
| 4 | 70 | C |
| 5 | 80 | B |
| 6 | 90 | A |
Formula: D1: =MATCH(84,A2:A6,1)
Result: 4
With 1, MATCH finds the largest value not above 84 (80), which is item 4. The list must be sorted ascending.
Is the value in the list?
| A | |
|---|---|
| 1 | Allowed |
| 2 | NY |
| 3 | CA |
| 4 | TX |
Formula: C1: =ISNUMBER(MATCH("CA",A2:A4,0))
Result: TRUE
MATCH returns a number when found and #N/A when not; ISNUMBER turns that into TRUE or FALSE.
Try it
A tiny spreadsheet with example data. Edit the formula or any number and see the result.
Formula in F2: =MATCH(103,A2:A6,0)
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | ID | Item | Price | Stock | Result | |
| 2 | 101 | Pen | 2.5 | 120 | 3 | |
| 3 | 102 | Notebook | 4 | 80 | ||
| 4 | 103 | Lamp | 25 | 15 | ||
| 5 | 104 | Mug | 7.5 | 40 | ||
| 6 | 105 | Backpack | 39 | 12 |
Result in F2: 3
Common errors and how to fix them
- #N/A. The value is not in the range. Check spelling, extra spaces and number-versus-text formats, or wrap in IFERROR.
- Wrong position on unsorted data. Match type 1 (the default if omitted) needs ascending data. Always type 0 for exact lookups.
- Wrong range shape. MATCH searches one row or one column. A two-dimensional range returns #N/A.
- Position is off by one. The number is relative to the first cell of the range. If the range starts at row 2, add 1 to get the sheet row.
Related functions
Frequently asked questions
- What does MATCH return?
- The relative position of the first matching item in the range, as a number.
- What do 0, 1 and -1 mean in MATCH?
- 0 is exact match; 1 finds the largest value not above the target in ascending data; -1 finds the smallest value not below the target in descending data.
- Does MATCH support wildcards?
- Yes, with match type 0 and text: "Sm*" matches "Smith".
- Is MATCH case sensitive?
- No. "ana" and "ANA" match each other.