MATCH Function
MATCH finds the position of a value in a range.
Syntax
Parameters
| Parameter | Description | Required |
|---|---|---|
| lookup_value | The value to search for. | Required |
| lookup_array | The range to search within. | Required |
| match_type | Optional. 1=approximate (sorted), 0=exact, -1=approximate (descending). | Optional |
Basic Example
Find position of a value
=MATCH("Apple", A1:A5, 0)Returns the position (3) where "Apple" is found in the range.
Advanced Examples
Example 1: MATCH with INDEX for two-way lookup
Advanced lookupCombine MATCH with INDEX for flexible lookups
=INDEX(B:B, MATCH(A1, C:C, 0))How MATCH Works
MATCH scans the lookup_array for the lookup_value and returns its relative position (1-based).
Important Notes & Limitations
MATCH returns position, not the actual value.
For returning the value, combine with INDEX.
Common Errors & Fixes
#N/AValue not found in rangeFix: Check that the value exists in the lookup range.
Download Practice File
MATCH
matchPractice MATCH with Real Data
Download a sample CSV file with pre-populated data and practice exercises for the MATCH function. Works in both Excel and Google Sheets.
File format: CSV (comma-separated values) - opens in Excel, Google Sheets, and all spreadsheet apps
Related Tutorials
INDEX MATCH Formula
Master the INDEX MATCH formula in Excel and Google Sheets step by step: exact syntax, left lookups, two-way lookups, and how it compares to VLOOKUP and XLOOKUP.
Master INDEX/MATCH for Two-Way Lookups
Learn how to use INDEX and MATCH together to create powerful two-way lookups that work in any direction, unlike VLOOKUP.
INDEX MATCH with Multiple Criteria
INDEX MATCH with multiple criteria lets you match on two or more columns in Excel and Google Sheets. Learn the exact array formula plus a cleaner XLOOKUP alternative.
INDIRECT Function: Build Dynamic References From Text
INDIRECT function dynamic reference: convert text into a live reference for dynamic sheet names, expandable SUM ranges and dependent dropdowns in Excel.
MATCH Function Exact Match
MATCH with match_type 0 is Excel's exact-match lookup. See how MATCH finds a precise position in a range and how to use it safely with INDEX and XMATCH.
OFFSET Function: Build a Dynamic Range That Grows With the Data
An offset function dynamic range that ends at the last filled row, plus the one blank cell that quietly cut a $639,800 total to $545,050, and the INDEX swap.
VLOOKUP Approximate Match
VLOOKUP approximate match explained: how TRUE range_lookup works, why data must be sorted ascending, tax and grade table examples, plus XLOOKUP alternatives.
VLOOKUP Return Multiple Columns
VLOOKUP is built to return one column, but you often need several. Learn four reliable ways to make VLOOKUP return multiple columns in Excel and Google Sheets.
VLOOKUP with Multiple Criteria
VLOOKUP only matches one column, but real lookups often need two or more. Learn four reliable ways to do a VLOOKUP with multiple criteria in Excel and Google Sheets.