FormuladrillMATCH
MATCH(lookup_value, lookup_array, [match_type]) returns the position (1, 2, 3...) of a value in a single row or column, not the value itself. Use match_type 0 for an exact match; it is most often used inside INDEX to build a lookup.
Syntax
MATCH(lookup_value, lookup_array, [match_type])| Part | What it is |
|---|---|
lookup_value | The value to find. Wildcards ? and * are allowed with match_type 0. |
lookup_array | One row or one column to search. |
match_type | 0 exact match. 1 (default) largest value <= lookup_value, array sorted ascending. -1 smallest value >= lookup_value, array sorted descending. |
Examples
=MATCH("Mar", A1:L1, 0)Result: 3 when A1:L1 holds Jan, Feb, Mar, ...
Position in the row, counted from 1.
=MATCH("P-102", A2:A50, 0)Result: The row number within A2:A50 where P-102 sits (7 if it is in A8)
=MATCH(45000, A2:A6, 1)Result: 4 when A2:A6 holds 0, 10000, 20000, 40000, 80000
Approximate match: position of the largest value not above 45000.
=MATCH("P-1*", A2:A50, 0)Result: Position of the first code starting with P-1
Wildcards only work with match_type 0.
Common mistakes
- Wrong position on an unsorted list: match_type was omitted, so Excel used 1. Always add 0 for exact matches.
- #N/A: the value is not present, or text against number. MATCH("102", ...) does not find the number 102.
- #N/A with a two-dimensional range: lookup_array must be a single row or a single column.
- Using the position as the answer: MATCH gives a position, wrap it in INDEX to get the value.
Practice MATCH
These drills check your answer on live data, free, no account needed.
- Finishing positionThe runners are listed in finishing order. Return the finishing position of the runner named in F1. Answer in F2.
- Look left with INDEX and MATCHThe employee name is in the last column, so VLOOKUP cannot reach the salary to its left. Use INDEX and MATCH to return the salary of the employee named in F1. Answer in F2.
Questions
What is the difference between MATCH and VLOOKUP?
MATCH returns where a value is; VLOOKUP returns a value from another column. MATCH is paired with INDEX to make a lookup that works in any direction.
Does MATCH care about upper and lower case?
No. MATCH("apple", ...) finds Apple and APPLE. For a case-sensitive match use MATCH(TRUE, EXACT(range, value), 0).
Related
- INDEXHow the Excel INDEX function returns the cell at a given row and column of a range, with examples for a single column, a grid and the pairing with MATCH.
- INDEX MATCHHow to combine INDEX and MATCH in Excel to look up a value from any column, left or right, with worked examples, a two-way lookup and the usual errors.
- XLOOKUPHow XLOOKUP works in Excel: find a value in one column and return the match from another, in any direction, with a not found value and next-smaller match.
- VLOOKUPHow to use VLOOKUP in Excel to find a value in the first column of a table and return a value from another column, with exact and approximate match examples.