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.
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.