How to Use MATCH Function in Excel

Learn to use the MATCH function to find the position of a value within a range or array. MATCH returns the relative position (1, 2, 3, etc.) of a matching item, making it essential for dynamic lookups, INDEX-MATCH combinations, and data validation tasks.

Why This Matters

MATCH is fundamental for creating flexible lookup formulas and is the backbone of advanced INDEX-MATCH solutions that outperform VLOOKUP in speed and versatility.

Prerequisites

•Basic understanding of Excel ranges and cell references
•Familiarity with formula syntax and function arguments

Step-by-Step Instructions

Open a new worksheet with sample data

Create a simple list of items (A1:A5) with values like Product A, Product B, etc., or use existing data in your workbook.

Click on the target cell for your formula

Select an empty cell (e.g., C2) where you want the MATCH result to appear.

Type the MATCH formula syntax

Enter =MATCH(lookup_value, lookup_array, match_type). Use 0 for an exact match. Use 1 for the largest value less than or equal to the lookup value, and sort that range ascending. Use -1 for the smallest value greater than or equal to the lookup value, and sort that range descending. If you omit match_type, Excel uses 1.

Define your lookup parameters

For example, type =MATCH("Product B",A1:A5,0) to find the position of "Product B" in your list, or reference a cell like =MATCH(B2,A1:A5,0) for dynamic lookups.

Press Enter and review the result

The formula returns a number representing the position (e.g., 2 if "Product B" is in the second row); verify it matches your expected outcome.

Alternative Methods

Use INDEX-MATCH combination

Wrap MATCH inside INDEX to retrieve the actual value instead of just the position: =INDEX(return_range, MATCH(lookup_value, lookup_range, 0)).

Use XMATCH (Excel 365)

XMATCH offers enhanced features like searching right-to-left and handling missing values with custom results.

Combine with IFERROR for error handling

Use =IFERROR(MATCH(...),"Not Found") to display custom text instead of #N/A errors when no match exists.

Tips & Tricks

✓Use match_type 0 for exact matches unless you have sorted data requiring approximate matches.
✓Combine MATCH with COLUMN() or ROW() to find positions in 2D arrays or multiple columns.
✓For case-sensitive matching, wrap MATCH with EXACT function: =MATCH(TRUE,EXACT(range,value),0).
✓Use absolute references ($A$1:$A$5) when copying formulas to prevent range shifts.

Pro Tips

★Pair MATCH with OFFSET to create dynamic ranges that shift based on matched positions.
★Use MATCH with wildcards (?) and (*) in match_type 0 to find partial text matches.
★For multi-criteria matching, use MATCH with CONCATENATE to search combined values.

Troubleshooting

MATCH returns #N/A

Check that the lookup value exists in the range by using Find (Ctrl+F). Ensure data types match (text vs. numbers) and remove extra spaces using TRIM().

MATCH finds wrong position with sorted data

You're likely using match_type 1 or -1 which require sorted data. Switch to match_type 0 for exact matches, or verify your data is properly sorted ascending/descending.

Formula breaks when range is modified

Use absolute references ($A$1:$A$100) instead of relative references to lock the range, preventing changes when inserting/deleting rows.

Related Excel Formulas

INDEX VLOOKUP XLOOKUP XMATCH LOOKUP SUMPRODUCT

Frequently Asked Questions

What's the difference between MATCH and VLOOKUP?

MATCH returns the position of a value, while VLOOKUP returns the value from a specified column. MATCH is more flexible when combined with INDEX, as it works with any column order.

Can MATCH search in multiple columns?

MATCH searches only one row or column at a time. For multi-column searches, use SUMPRODUCT or create helper columns with MATCH logic.

What does match_type 1, 0, and -1 mean?

Match_type 0 finds exact matches. Type 1 finds the largest value less than or equal to lookup_value (requires ascending sorted data). Type -1 finds the smallest value greater than or equal to lookup_value (requires descending sorted data).

How do I use MATCH with partial text matches?

Use wildcards with match_type 0: =MATCH("Product*",A1:A5,0) finds the first cell starting with "Product". However, this matches only the first occurrence.