For IndividualsFor Analysts & FinanceFor Students & LearnersFor Teams & Automation
MENU
=FUNCTIONS

MATCH()

Lookup & Reference

Returns the relative position of a value within a range, rather than the value itself.

MATCH(lookup_value, lookup_array, [match_type])

MATCH tells you where a value sits in a range, counting from 1 — not the value at that position. That position is exactly what INDEX needs as its row_num, which is why the two are almost always used together.

match_type 0 finds an exact match in any order; 1 (the default) finds the largest value less than or equal to lookup_value in data sorted ascending; -1 does the mirror image on data sorted descending.

The argument that causes most MATCH bugs is the third one. Omitted, it defaults to 1, an approximate match that assumes the lookup range is sorted ascending and silently returns the wrong position if it is not. Always type the 0. The second reason MATCH still matters on modern Excel is that it finds positions rather than values: feeding its result into INDEX, OFFSET or a column index keeps a formula pointing at the right column after somebody inserts one, which is the most common way a hard-coded lookup quietly starts returning the wrong field.

Arguments

lookup_value
The value to find.
lookup_array
The range to search — a single row or column.
match_typeoptional
0 for exact match, 1 for largest ≤ (ascending data), -1 for smallest ≥ (descending data).

Examples

Excel worksheet with a list of months and a MATCH formula returning the position of March, with a callout on how MATCH pairs with INDEX.
=MATCH("March", A1:A12, 0)
→3

March is the 3rd item in the range — an exact match.

=INDEX(B1:B12, MATCH("March", A1:A12, 0))
→March's value from column B

The classic INDEX/MATCH pairing.

=MATCH(TRUE, INDEX(B2:B500<>"", 0), 0)
→7

Finds the position of the first non-empty cell — useful for locating where a column of data actually starts after a ragged import.

Notes and gotchas

  • The third argument defaults to 1 (approximate, requires ascending sort) when omitted — the same trap as VLOOKUP's fourth argument. Type 0 for exact match.
  • MATCH returns a position within the range, not a value. Position 3 of B5:B50 is cell B7, not row 3.
  • With match_type of 0, wildcards * and ? are active. If a lookup value legitimately contains an asterisk, escape it with ~.
  • MATCH(TRUE, ...) over a calculated array is the classic way to find the first row meeting a condition that is not a simple equality.

Common errors

What goes wrong with MATCH, and why
You seeBecauseFix
#N/ANo exact match, or match_type is 1 and the data is not sorted ascending.Set match_type to 0 explicitly, then check the key for whitespace and type mismatches.
#VALUE!lookup_array spans more than one row and one column.MATCH takes a single row or single column. Use two MATCHes inside INDEX for a two-way lookup.
Tips / Tricks / Hacks

MATCH tricks, tips, and workbook hacks

MATCH does not return a cell's value—it returns the relative position (1, 2, 3...) where an item appears inside a single row or single column. That single number is what turns static formulas into dynamic ones: feed a MATCH result into INDEX, CHOOSE, VLOOKUP, or OFFSET and your workbook adapts automatically when headers move or users pick a different scenario.

The critical habit with MATCH is explicit control of match_type: 0 for exact match (95% of business lookups), 1 for largest value less than or equal to the target on ascending data, and -1 for smallest value greater than or equal on descending data.

Quick MATCH checks before trusting the result

  • Always include ,0 as the 3rd argument unless you are deliberately doing a sorted threshold search.
  • Use ISNUMBER(MATCH(A2, ListRange, 0)) as the cleanest, fastest way to test whether an item exists in another list.
  • Remember that MATCH counts from the start of lookup_array, not from row 1 or column A of the worksheet.
  • Use wildcards ("*"&A2&"*") with match_type 0 when searching for a code embedded inside a longer text string.
MATCH tricks and formula patterns
MATCH patternMATCH formulaBest useWatch out
Boolean membership check across two lists=ISNUMBER(MATCH(A2,$F$2:$F$1000,0))Conditional formatting rules, data validation checks, or flagging whether an invoice ID has already been paid.Faster and cleaner than COUNTIF when you only care whether at least one match exists.
Dynamic column number for legacy VLOOKUP=MATCH(C$1,$A$1:$Z$1,0)Replacing hardcoded column numbers inside VLOOKUP so inserted columns never break reports.The header range in MATCH must start at the exact same column as VLOOKUP's table_array.
Compare two columns to spot missing items=IF(ISNA(MATCH(A2,$D$2:$D$500,0)),"Missing from System B","Matched")Bank reconciliations, payroll audits, and inventory count comparisons.Wrap lookup keys in TRIM() if one system exports trailing spaces.
Find the row number of the first negative cash flow=MATCH(TRUE,C2:C100<0,0)Identifying the exact period where inventory runs out or cash balance dips below zero.In Excel 2019 and earlier, array expressions inside MATCH require Ctrl+Shift+Enter.
Common MATCH mistakes and how to fix them
MATCH mistakeWhat happens in MATCHFix
Passing a 2D block (e.g. A2:C100) to lookup_arrayMATCH immediately returns #N/A even when the value sits right inside A2:C100.Restrict lookup_array to a single column (A2:A100) or a single row (A1:Z1).
Confusing relative position with worksheet row numberMATCH finds an item in A10:A50 at row 12 and returns 3, which you mistakenly treat as row 3.Add ROW(A10)-1 to the result if you need the absolute worksheet row number, or start both INDEX and MATCH at row 10.
Leaving match_type blank on text headersMATCH returns the wrong column number when headers are not in alphabetical order.Never omit the 3rd argument—type ,0 explicitly.

MATCH practice drills

  1. Create two lists of 15 order IDs with 3 mismatches. Use =ISNUMBER(MATCH(...)) to highlight the 3 missing IDs with Conditional Formatting.
  2. Place a lookup list starting in row 5 (B5:B20) and observe the difference between the MATCH position and the Excel row number.
  3. Use MATCH with a wildcard ("*East*") to locate a regional header that includes a territory code.

Related functions