How to How to Use XMATCH Function in Excel in Excel - Practical Guide

Learn how to use XMATCH, Excel's powerful lookup function that finds the position of a value in a range with flexible search options. XMATCH improves upon MATCH by offering bidirectional search, wildcard support, and exact/approximate matching—essential for advanced data analysis and dynamic formulas.

Why This Matters

XMATCH is crucial for professionals handling large datasets who need flexible, reliable lookups without the limitations of older functions like MATCH or INDEX/MATCH combinations.

Prerequisites

•Basic Excel knowledge and familiarity with cell references
•Understanding of lookup functions (VLOOKUP or INDEX/MATCH helpful)
•Excel 365 or Excel 2021+ (XMATCH availability)

Step-by-Step Instructions

Open Your Spreadsheet

Open Excel and create or load your data file containing the lookup range and search value.

Select the Target Cell

Click the cell where you want the XMATCH result to appear (typically where you need the position number).

Enter the XMATCH Formula

Type: =XMATCH(lookup_value, lookup_array, [match_mode], [search_mode]). For example: =XMATCH("Apple", A2:A10, 0, 1) searches for exact match in ascending order.

Configure Match Mode

Specify match_mode: 0 for exact match, -1 for exact match or next smallest, 1 for exact match or next largest, or 2 for wildcard match.

Press Enter and Verify Results

Press Enter to execute the formula; it returns the position number of the matched value in the lookup array.

Alternative Methods

Use with INDEX for Full Lookup

Combine XMATCH with INDEX: =INDEX(return_range, XMATCH(lookup_value, lookup_array)) to retrieve actual values instead of just positions.

Traditional MATCH Function

For older Excel versions, use =MATCH(lookup_value, lookup_array, 0) though it lacks XMATCH's flexibility and wildcard capabilities.

VLOOKUP Alternative

VLOOKUP searches left-to-right but is limited to rightward column returns; XMATCH offers bidirectional search for more complex data layouts.

Tips & Tricks

✓Use match_mode 0 for exact matches when you need precise data retrieval without approximations.
✓Set search_mode to -1 for descending searches and 1 for ascending to optimize performance on sorted data.
✓Combine XMATCH with IFERROR to handle missing values gracefully: =IFERROR(XMATCH(...), "Not Found").
✓Use wildcard matching (mode 2) with * and ? for partial text searches across large datasets.

Pro Tips

★Nest XMATCH inside INDIRECT to create dynamic references that adjust based on user input or cell values.
★Use search_mode 2 (binary search) on large sorted ranges for significant performance improvements over standard sequential search.
★Combine XMATCH with SMALL or LARGE to find nth occurrence of values in complex datasets.
★Leverage XMATCH's bidirectional capability to search both left and right from a central position without helper columns.

Troubleshooting

XMATCH returns #NAME? error

Your Excel version doesn't support XMATCH; upgrade to Excel 365 or 2021+, or use MATCH function instead.

Formula returns #N/A error

The lookup value wasn't found in the array; verify the value exists and check for extra spaces or case sensitivity issues using TRIM or LOWER functions.

XMATCH returns wrong position number

Verify your match_mode setting is correct for your data type (exact vs. approximate) and ensure lookup_array is properly sorted if using approximate matching.

Search appears slow on large datasets

Use search_mode 2 for binary search on sorted data, or optimize by filtering the lookup range to smaller subsets before searching.

Related Excel Formulas

INDEX MATCH VLOOKUP XLOOKUP IFERROR INDIRECT

Frequently Asked Questions

What's the difference between XMATCH and MATCH?

XMATCH offers bidirectional search, wildcard support, and more match modes than MATCH. MATCH only searches forward and lacks advanced features, making XMATCH superior for modern Excel work.

Can I use XMATCH with multiple criteria?

XMATCH handles single criteria natively, but you can combine it with helper columns or array formulas to match multiple conditions across your data.

How do I return the actual value instead of the position?

Use INDEX: =INDEX(return_range, XMATCH(lookup_value, lookup_array)). This returns the value at the position XMATCH finds.

Is XMATCH available in older Excel versions?

No, XMATCH requires Excel 365 or Excel 2021 and later; older versions must use MATCH or INDEX/MATCH combinations.

What does search_mode 2 (binary search) do?

Binary search divides sorted data in half repeatedly for faster lookups; use it only on sorted arrays to significantly improve performance on large datasets.