Excel MATCH Function: Syntax, Examples, and Tips
Master the Excel MATCH function to locate a value's position in a range, handle exact and approximate matches, and pair with INDEX for dynamic lookups. Includes practical examples, error handling, and best practices.
Definition: The Excel MATCH function returns the position of a specified lookup value within a one-dimensional range (row or column). It supports exact matches (match_type 0) and approximate matches (1 or -1, with sorting requirements). It’s commonly combined with INDEX to retrieve corresponding data from a table, enabling flexible lookups. Example: =MATCH("Apple", A2:A20, 0).
What the MATCH function does
The MATCH function searches a single row or column for a specified value and returns its position, not the value itself. This positional result makes MATCH perfect for downstream lookups with INDEX, VLOOKUP alternatives, or dynamic range selection. In practice, you might want to locate a product name in a sales list and then pull the associated price from a separate column. The key is understanding that MATCH returns a relative index within the lookup array, which you can feed into INDEX to retrieve the actual item data.
=MATCH("Widget A", A2:A100, 0)- This exact-match lookup returns the position where Widget A appears in A2:A100. The result is a number like 7 if Widget A sits in A8.
=INDEX(B2:B100, MATCH("Widget A", A2:A100, 0))- INDEX uses the position from MATCH to return the corresponding value from B2:B100. If Widget A is in A37, this formula returns B37.
=MATCH("Orange", B2:B100, 0)- Using MATCH on a second column allows cross-field lookups when data is aligned in a table. For multi-column tables, you can combine two MATCH calls with INDEX to resolve both row and column:
=INDEX(B2:Z100, MATCH("Widget A", A2:A100, 0), MATCH("Price", B1:Z1, 0))Explanation_placeholder_for_section_regarding_algorithms_and_variants
Steps
Estimated time: 60-90 minutes
- 1
Prepare data and goals
Organize your data in a clean table with a clear lookup column and a value column. Decide whether you need an exact or approximate match, and identify the result you want to retrieve (e.g., a price, a name, or a category).
Tip: Use a named range for the lookup column to improve readability. - 2
Choose the match_type
If you need an exact match, set match_type to 0. For simple ranking, use 1 (ascending) or -1 (descending) with caution about sort order.
Tip: Avoid mixing data types in the lookup column to prevent mismatches. - 3
Write the MATCH formula
Create a base formula: =MATCH(lookup_value, lookup_array, match_type). Validate that the lookup_value exists in the array by testing with a known example.
Tip: Start with a small, shaded sample to verify behavior before applying to large data. - 4
Combine with INDEX
Feed the MATCH output into an INDEX to retrieve the corresponding value from another column: =INDEX(return_range, MATCH(...)).
Tip: When returning from a multi-column range, add an additional MATCH for the column header to pick the right column. - 5
Add error handling
Wrap with IFERROR or IFNA to gracefully handle not-found cases: =IFERROR(INDEX(...), "Not found").
Tip: Consider displaying a helpful message or leaving the cell blank for dashboard cleanliness. - 6
Test edge cases
Test with duplicates, blank values, and numeric-as-text data to ensure robust results. If needed, normalize data types first.
Tip: If you expect duplicates, consider returning the first match or using a helper to aggregate results.
Prerequisites
Required
- Required
- Basic knowledge of cell references and rangesRequired
Optional
- Optional
- A sample workbook with data to practice lookupsOptional
Keyboard Shortcuts
| Action | Shortcut |
|---|---|
| Insert a MATCH formulaStart by typing '=' in the target cell to begin a formula; place MATCH inside. | ↵ |
| Open the Format Cells dialogUse when you need to adjust data types or formatting that affect lookups. | Ctrl+1 |
| Fill the formula downExtend a MATCH or INDEX/MATCH formula to adjacent rows. | Ctrl+D |
| Show formulas for debuggingToggle to inspect all formulas during troubleshooting. | Ctrl+` |
People Also Ask
What does the MATCH function return in Excel?
MATCH returns the position of the lookup value within a one-dimensional range (row or column). It does not return the actual value found. Use INDEX with MATCH to retrieve related data from another column or row.
MATCH gives you the position of the item, not the item itself, so pair it with INDEX to fetch the data you want.
How do you use MATCH with INDEX to retrieve data?
Use INDEX to return the value at the position provided by MATCH. Example: =INDEX(Prices!C2:C100, MATCH("Widget A", Prices!A2:A100, 0)). This retrieves the price for Widget A from the data table.
You combine INDEX and MATCH to pull data from a table using the position MATCH finds.
Can MATCH handle wildcards?
Yes, when using match_type 0 (exact match). You can use wildcards like * and ? in the lookup_value to match text patterns.
Wildcards work with exact matches in MATCH, letting you find patterns in text.
What happens if the lookup value is not found?
MATCH returns #N/A when no match is found. You can wrap the function in IFNA or IFERROR to handle this gracefully.
If the value isn’t in the range, you’ll get #N/A, but you can trap it with IFNA or IFERROR.
Is MATCH case-sensitive?
No. MATCH is not case-sensitive by default. If you need case-sensitive results, you’ll need a different approach (e.g., using EXACT in combination with MATCH).
MATCH doesn’t care about letter case unless you add extra tests.
When should I use XMATCH or XLOOKUP instead of MATCH?
XMATCH and XLOOKUP offer built-in features like search modes and better error handling. They are available in newer Excel versions and often simplify multi-lookup scenarios compared to legacy MATCH.
If you have a modern Excel, XMATCH or XLOOKUP can simplify lookups and improve reliability.
The Essentials
- Use MATCH to get a position, not the value itself.
- Pair MATCH with INDEX for flexible lookups across tables.
- Handle missing values with IFERROR to keep dashboards clean.
- Choose match_type carefully based on data sorting and needs.