Arguments
lookup_value- The value whose position you want.lookup_array- The range to search.match_type(optional) - 0 exact (recommended), 1 largest ≤ value, -1 smallest ≥ value.
MATCH
Returns the position of a value within a range, rather than the value itself.
Syntax
=MATCH(lookup_value, lookup_array, [match_type])=MATCH(A2, C2:C100, 0)Returns: The row number where C equals A2
Exact match returns a position you can feed into INDEX.
How MATCH evaluates
MATCH searches a one-dimensional range and returns the position of the hit, counted from 1 at the start of that range. It never returns the value it found. That is the point: a position can be fed to INDEX to read any column, used to test whether an item exists at all, or compared against another MATCH to check that two lists are in the same order.
The third argument, match_type, changes the search completely and defaults to 1 rather than 0 - the same trap as VLOOKUP's range_lookup. match_type 1 finds the largest value less than or equal to the lookup value and requires the range sorted ascending. match_type -1 finds the smallest value greater than or equal to it and requires descending order. match_type 0 is a plain scan for an equal value on data in any order, and is what you want in almost every lookup.
The lookup range must be a single row or a single column. A rectangular range gives #N/A rather than searching row by row, which is a useful constraint: it forces the search dimension to be explicit. XMATCH, added in Excel 2021, is the same function with an exact default and an extra search_mode argument for searching backwards.
- Position, not row number
- =MATCH(x, A5:A40, 0) returning 3 means the third cell of that range, which is A7. Feed it to INDEX over a range that starts on the same row and the offset takes care of itself; combine it with a range that starts elsewhere and every result is silently shifted.
- match_type 1 is the default
- Omit the third argument and you get approximate ascending match, not exact. On unsorted data it returns whatever position it reached before the values stopped increasing - a wrong answer with no error. Type the 0.
- Exact match supports wildcards
- With match_type 0 and a text lookup value, * matches any run of characters and ? matches one. =MATCH("*Ltd", A2:A50, 0) finds the first company name ending in Ltd. Escape a literal * or ? with ~.
- Case-insensitive text comparison
- MATCH does not distinguish "north" from "NORTH". For a case-sensitive position, use =MATCH(TRUE, EXACT(range, value), 0) entered as an array formula, or MATCH over an exact-comparison helper column.
- Duplicates return the first hit
- With match_type 0 the scan runs top to bottom and stops at the first equal value. To find the last occurrence, either use XMATCH with search_mode -1 or count the occurrences and offset from the first.
- Blanks are skipped, not matched
- Empty cells inside the lookup range do not stop an approximate search and do not match a blank lookup value the way an equals comparison would. A range with gaps is a poor candidate for match_type 1 or -1.
Errors MATCH returns
| Error | What it means and how to fix it |
|---|---|
| #N/A | Nothing matched. With match_type 0 that means the value genuinely is not there, or there is a type or whitespace mismatch. With match_type 1 it means every value in the range is greater than the lookup value, and with -1 that every value is smaller. It is also what a rectangular lookup range returns, since MATCH searches only vectors. |
| #VALUE! | match_type is text that cannot be read as a number, or the lookup value is an array where a single value was expected. Check that a nested formula supplying the lookup value returns one cell. |
| #REF! | The lookup range points at a deleted range or a closed workbook. Repoint it at a live range on an open sheet. |
| #NAME? | The function is misspelled, or a named range used as lookup_array does not exist. XMATCH shows this in Excel 2019 and earlier, where it is not available. |
| Wrong position, no error | match_type was left blank or set to 1 on an unsorted range. Sort the range ascending if approximate matching is intended, otherwise pass 0. |
More examples
=IF(ISNA(MATCH(A2, $D$2:$D$500, 0)), "Missing", "Present")Returns: "Missing" or "Present"
Using MATCH purely as an existence test. ISNA is cheaper to read than a COUNTIF and makes the intent obvious.
=MATCH(MAX($B$2:$B$100), $B$2:$B$100, 0)Returns: The position of the largest value
Feeding MAX back into MATCH locates the top row, which INDEX can then read a name from. Ties resolve to the first occurrence.
=MATCH(A2, $C$2:$C$50) - MATCH(A2, $C$2:$C$50, 0)Returns: 0 when the range is sorted around A2
Comparing the approximate and exact positions is a quick audit: any non-zero result means the approximate search landed somewhere the exact search did not, so the range is not sorted the way match_type 1 assumes.
Compatibility
| Application | Support |
|---|---|
| Excel 2007-2016 | Fully supported with all three match types. XMATCH is not available. |
| Excel 2019 | Identical. INDEX/MATCH is the standard lookup pattern in this version. |
| Excel 2021 / Microsoft 365 | Unchanged, and joined by XMATCH, which defaults to exact match and can search last-to-first. |
| Google Sheets | Same syntax, same default of 1. XMATCH is also available in Sheets. |
| LibreOffice Calc | Supported; wildcard behaviour in match_type 0 follows the wildcards or regular expressions setting under Tools, Options, Calc, Calculate. |
Does MATCH Work in Google Sheets?
Yes. MATCH uses the same syntax in Google Sheets and LibreOffice Calc, so you can paste these formulas straight across.
In the ExcelTool editor
The ExcelTool editor implements MATCH with two or three arguments and defaults match_type to 1, matching Excel: omit it and the search is an ascending approximate match, not an exact one.
match_type 0 scans for an equal value, is case-insensitive, and treats a lookup value containing * or ? as a wildcard pattern. Any positive match_type takes the last key at or below the lookup value; any negative one takes the last key at or above it. Blank cells in the range are skipped by both approximate searches.
The search direction is inferred from the shape of the range - taller than wide is searched as a column, otherwise as a row - so the editor will search a rectangular range's first column rather than returning #N/A as Excel does. XMATCH is also available but always performs an exact match and ignores its third argument.
You can try any of these formulas in the free in-browser spreadsheet editor - it opens .xlsx files, recalculates as you type, and needs no signup.
Build a MATCH formula
Fill in your ranges and copy a ready-to-paste formula - no signup.
MATCH questions
Because the default is 1, not 0. That runs an approximate ascending search: MATCH walks the range until the values stop being less than or equal to your lookup value, then reports the last position it accepted. On an unsorted list it stops somewhere arbitrary and returns that position with no error at all. Always type the 0 unless the range is sorted ascending and you deliberately want banded matching.
In Excel 2021 or 365, use =XMATCH(value, range, 0, -1) - the fourth argument searches from the end. On older versions the usual trick is =MATCH(2, 1/(range=value)), which builds an array of 1s and errors and relies on approximate match running off the end of the 1s, returning the last position where the condition held.
No. lookup_array must be one row or one column; hand it a rectangle and Excel returns #N/A. For a two-way lookup, run two MATCH calls - one down the row keys and one across the column headers - and pass both to INDEX. If you need the position within a rectangle as a single number, INDEX over a flattened array or a helper column is the practical route.
Usually yes, and for a structural reason: MATCH stops at the first hit, while COUNTIF must read every cell in the range to finish counting. Over a long column checked thousands of times that difference is measurable. Wrap MATCH in ISNUMBER or ISNA to turn the position into the TRUE/FALSE that the test actually wanted.