Lookup & Reference

MATCH Function in Excel

Returns the relative position of an item in an array that matches a specified value.

Syntax

  • =MATCH(lookup_value, lookup_array, [match_type])

Arguments

  • lookup_value (required): The value to look for
  • lookup_array (required): The range to search
  • match_type (optional): 1=less than, 0=exact, -1=greater than

Examples

  • =MATCH("Apple", A1:A20, 0) - Find position of Apple (exact match) - Result: 5
  • =MATCH(100, B:B, 1) - Find position of value <= 100 - Result: Position number

MATCH in lookup formulas

  • Use MATCH to find the position of a lookup value inside a row, column, or array.
  • Combine MATCH with INDEX when you need dynamic row or column selection.

match_type reference

  • 0 = exact match — safest for IDs, SKUs, and labels.
  • 1 = largest value less than or equal to lookup — lookup column must be sorted ascending.
  • -1 = smallest value greater than or equal to lookup — lookup column must be sorted descending.

Sorted-data warnings

  • Approximate match modes return wrong positions if the column is not sorted as required.
  • If data is unsorted, always use 0 for exact match or switch to XLOOKUP with explicit match_mode.
  • Microsoft 365 users can evaluate XMATCH for wildcard and reverse search options.

People also ask

  • What does 0 mean in MATCH? — Exact match — use for IDs and codes.
  • Can MATCH search a row? — Yes; pass a horizontal lookup_array.

Excel MATCH function documentation

  • MATCH is a Lookup & Reference function that returns the position (row or column number) of a value inside a range — not the value itself.
  • Syntax: =MATCH(lookup_value, lookup_array, [match_type]). Available in all modern Excel versions.
  • Pair with [INDEX](/functions/index/) for flexible lookups: =INDEX(return_range, MATCH(lookup_value, lookup_range, 0)).
  • Microsoft 365 users can evaluate [XMATCH](/functions/xmatch/) for wildcard search, reverse search, and binary modes.

MATCH syntax and match_type explained

  • lookup_value (required): the value to find — ID, name, date, or cell reference.
  • lookup_array (required): a single row or column to search (not a full table).
  • match_type 0 (recommended): exact match — use for IDs, SKUs, and labels.
  • match_type 1: largest value ≤ lookup_value — lookup_array must be sorted ascending (tax brackets, commission tiers).
  • match_type -1: smallest value ≥ lookup_value — lookup_array must be sorted descending.

Step-by-step: build INDEX MATCH with MATCH

  • Step 1 — Put product IDs in column A and prices in column C on the same rows.
  • Step 2 — Enter the ID to find in E2.
  • Step 3 — Price formula: =INDEX(C:C, MATCH(E2, A:A, 0)).
  • Step 4 — Lock ranges with $ when copying: =INDEX($C$2:$C$500, MATCH(E2, $A$2:$A$500, 0)).
  • Step 5 — If #N/A appears, confirm exact match (0), TRIM spaces, and matching data types.

MATCH vs XMATCH vs VLOOKUP

  • MATCH — classic position lookup; works in older Excel; requires INDEX for the return value.
  • XMATCH — modern replacement with wildcards, last-match search, and binary search on sorted data.
  • VLOOKUP — returns the value directly but only from columns to the right of the key.
  • Tutorial: [INDEX MATCH guide](/blog/index-match-excel-guide/) · Compare [INDEX MATCH vs XLOOKUP](/compare/index-match-vs-xlookup/).

Worked examples to copy

  • Row position for INDEX: =MATCH("Widget", A2:A100, 0) returns the row offset within A2:A100.
  • Column header lookup: =MATCH("Q3", B1:M1, 0) finds which column holds the Q3 label.
  • Approximate tax bracket: =MATCH(income, bracket_table, 1) on sorted ascending thresholds.
  • Two-way table: =INDEX(data, MATCH(row_label, rows, 0), MATCH(col_label, headers, 0)).

People also ask

  • What does MATCH return in Excel? — The relative position (number) of a match within lookup_array.
  • Why does MATCH return #N/A? — Value not found, wrong match_type, or unsorted data for approximate modes.
  • Can MATCH search a row? — Yes. Pass a horizontal range as lookup_array.
  • MATCH vs XMATCH? — XMATCH adds wildcard, reverse, and binary search; requires Excel 2021/M365.

Common errors

  • #N/A when no match found

Use cases

  • Position finding
  • Combined with INDEX
  • Dynamic references

Frequently asked questions

  • What is the MATCH function in Excel? MATCH returns the relative position of a value within a range. The syntax is =MATCH(lookup_value, lookup_array, [match_type]). For example, =MATCH("Apple", A1:A10, 0) returns the row number where "Apple" is found (e.g., 5 if Apple is in A5). MATCH is commonly combined with INDEX for powerful lookups.
  • What do the match_type values 0, 1, and -1 mean? The match_type argument controls how MATCH searches: 0 = exact match (most common, data can be unsorted); 1 = largest value less than or equal to lookup_value (data must be ascending); -1 = smallest value greater than or equal to lookup_value (data must be descending). Always use 0 for exact matches to avoid unexpected results.
  • How do I use MATCH with INDEX for lookups? Combine INDEX and MATCH for flexible lookups: =INDEX(return_range, MATCH(lookup_value, lookup_range, 0)). MATCH finds the position, INDEX returns the value at that position. Example: =INDEX(B:B, MATCH("Apple", A:A, 0)) finds Apple's position in column A and returns the corresponding value from column B.
  • What is the difference between MATCH and XMATCH? XMATCH (Excel 365/2021) is an enhanced version of MATCH with additional features: 1) Search modes for first-to-last or last-to-first; 2) Binary search for sorted data; 3) Wildcard matching built-in; 4) Better error handling. MATCH is available in all Excel versions, while XMATCH requires Excel 365 or 2021.
  • Why is MATCH returning #N/A error? MATCH returns #N/A when the value isn't found. Common causes: 1) Value doesn't exist in the range; 2) Extra spaces - use =MATCH(TRIM(value), range, 0); 3) Data type mismatch (text vs number); 4) Using match_type 1 or -1 with unsorted data; 5) Case doesn't matter for MATCH, but formatting might. Always verify your lookup value exists in the data.

Editorial review

  • Reviewed by Excel.Directory Editorial Team. Updated May 2026.

When to use MATCH

  • Position finding — common Lookup & Reference scenario for MATCH.
  • Combined with INDEX — common Lookup & Reference scenario for MATCH.
  • Dynamic references — common Lookup & Reference scenario for MATCH.

MATCH in the Lookup & Reference category

  • Browse all Lookup & Reference functions at /categories/lookup-reference/ for related formulas.
  • MATCH syntax: =MATCH(lookup_value, lookup_array, [match_type])
  • lookup_value (required): The value to look for
  • lookup_array (required): The range to search
  • match_type (optional): 1=less than, 0=exact, -1=greater than
  • Confirm MATCH arguments match the syntax shown above before filling down.
  • Lock table and range references with $ when copying formulas across rows or sheets.

Formula checklist before you copy down

  • Confirm MATCH arguments match the syntax shown above before filling down.
  • Lock table and range references with $ when copying formulas across rows or sheets.
  • If results look wrong, check for text stored as numbers and invisible spaces with TRIM.
  • Spot-check three known input rows manually against expected output.

Related Excel functions

  • INDEX (/functions/index/): Returns the value of an element in a table or array, selected by the row and column number indexes.
  • XLOOKUP (/functions/xlookup/): Searches a range or an array, and returns an item corresponding to the first match it finds. If a match doesn't exist, XLOOKUP can return the closest (approximate) match.
  • XMATCH (/functions/xmatch/): Returns the relative position of an item in an array or range of cells. Supports exact match, approximate match, and wildcard matching.
  • SEARCH (/functions/search/): Finds one text string within another and returns the starting position (case-insensitive, supports wildcards).
  • FIND (/functions/find/): Finds one text string within another and returns the starting position (case-sensitive).

Comparisons involving this function

  • XLOOKUP vs VLOOKUP (/compare/xlookup-vs-vlookup/): Use XLOOKUP for modern Excel workbooks when available. Use VLOOKUP when you need compatibility with older Excel versions or existing legacy files.
  • INDEX MATCH vs XLOOKUP (/compare/index-match-vs-xlookup/): Use XLOOKUP for readability and speed of setup. Use INDEX MATCH when maintaining older files or when you need a structure already built around separate row and column logic.
  • XLOOKUP vs INDEX MATCH (/compare/xlookup-vs-index-match/): Use XLOOKUP for most modern lookup tasks. Use INDEX MATCH when maintaining legacy workbooks or when row and column matching are already separated in the model.

Errors to watch for

  • #N/A when no match found — review causes on linked error pages in the directory.

Copy-paste audit workflow

  • Enter MATCH on three test rows with known expected output documented on a QA tab.
  • Fill down only after absolute references are locked on lookup tables and rate tables.
  • Compare against manual calculation or a calculator for financial and statistical functions.
  • Search this directory for comparison guides when choosing between similar functions in the same category.

MATCH worked examples to copy

  • =MATCH("Apple", A1:A20, 0) — Find position of Apple (exact match). Expected result: 5.
  • =MATCH(100, B:B, 1) — Find position of value <= 100. Expected result: Position number.

MATCH reference summary for crawlers and offline review

  • MATCH belongs to the Lookup & Reference category in Excel. Returns the relative position of an item in an array that matches a specified value.
  • Full syntax: =MATCH(lookup_value, lookup_array, [match_type]). Open /functions/match/ for parameters, FAQs, and related pages.
  • Common mistakes: #N/A when no match found
  • Pair this function with comparison guides when another Excel formula might fit the same task better.
  • Review fix-excel-formula-errors when unexpected errors appear after upgrading Excel or sharing across locales.