Back to functions
Lookup & Reference2026-11-161 related article

XMATCH Function in Excel

Find the relative position of an item in a row or column, using an exact match by default.

Syntax

XMATCH(lookup_value, lookup_array, [match_mode], [search_mode])
Table of Contents

Arguments

lookup_value

Required

The value to find in the search range.

lookup_array

Required

The row or column of values to search.

match_mode

Optional

Use 0 for exact match (default), -1 for exact or next smaller, 1 for exact or next larger, or 2 for wildcard matching.

search_mode

Optional

Use 1 to search first to last (default), -1 for last to first, 2 for binary search on ascending data, or -2 for binary search on descending data.

What it returns

The matched item's position within the search range, counted from its first cell.

What XMATCH does

XMATCH tells you where a value appears in a row or column. Its result is a position within the searched range, starting at 1. You can pass that position to another function, such as INDEX or CHOOSECOLS, to retrieve the corresponding data.

An exact match is the default. You don't need to sort the range for the ordinary first-to-last exact search.

Practical examples

Find the revenue column

Create a Table named Orders with the headers SKU, Region, Units, Revenue, and Rep in that order. In a cell outside the Table, enter:

=XMATCH("Revenue",Orders[#Headers])
Formula

The expected result is 4, because Revenue is the fourth header. It doesn't matter which worksheet column the Table starts in: the count starts at the Table's first header.

The header-based column selection tutorial uses this position to select the right field for a report.

Choose the first or last repeated label

In a separate ordinary range, enter SKU, Region, Region, and Revenue in B4:E4. Use a plain range for this example because Excel Tables require unique column names.

=XMATCH("Region",B4:E4,0)
Formula

The expected result is 2, the position of the first Region in C4. To search from the other end, use:

=XMATCH("Region",B4:E4,0,-1)
Formula

The expected result is 3, identifying Region in D4. Reversing the search changes which match is found; positions are still counted from B4, the start of the range.

Common mistakes and notes

A position isn't the matched value

The first example returns 4, not the text Revenue or a worksheet address. Use XLOOKUP when you want to retrieve a corresponding value directly, or pass XMATCH's position to another function.

Match mode and search mode do different jobs

The third argument controls what counts as a match. The fourth controls how the range is searched. To find the last exact match, use 0,-1; putting -1 in the third argument instead asks for an exact match or the next smaller value.

Binary searches require the correct sorting

Search mode 2 requires ascending data; -2 requires descending data. Microsoft warns that unsorted input can produce invalid results in these modes. Use the default search unless you have deliberately prepared the range for a binary search.

Wildcards need match mode 2

In wildcard mode, * matches a sequence of characters and ? matches one character. Use ~ to escape a wildcard character you want to match literally. The default exact-match mode doesn't enable wildcard matching. An exact search with no matching value returns #N/A; check the requested text and source entries before changing the match rule.

Related functions

Related articles

Deep dives, troubleshooting guides, and practical examples that use XMATCH.

Official documentation