XMATCH returns the position (a number) of a lookup value within a one-dimensional array or range. It is the modern replacement for MATCH, with extra match and search modes that make lookups more flexible and reliable.
=XMATCH(lookup_value, lookup_array, [match_mode], [search_mode])What does XMATCH do?
XMATCH is a Lookup & Reference function that tells you where a value sits in a list. Give it a value to find and a single row or column to search, and it returns a number: 1 for the first item, 2 for the second, and so on. If the value is not there, it returns an error such as #N/A. Think of it as the modern version of MATCH, with more control over how the match is made and which direction Excel searches.
Position numbers are more useful than they sound. Once you know that a product is the fourth item in a price list, you can feed that number into INDEX to pull the matching price, or into CHOOSEROWS to grab the whole row. XMATCH also supports exact matching, approximate matching, wildcards, and searching from the end of a list, which solves real problems like finding the most recent entry for a customer.
A typical office situation: you have a column of employee names and a separate column of desk numbers. You want to know the desk position for a specific person so you can pull related details from another table. XMATCH gives you that position in one formula, and it works the same way whether your list runs down a column or across a row.
When to use XMATCH
- Finding the row number of a customer or product in a long list before using INDEX.
- Locating the most recent entry for an item by searching from the bottom of a column.
- Checking whether a value exists in a list without sorting the data.
- Returning the position of the last matching value in a column of transactions.
- Matching values approximately, such as finding the correct tax bracket or discount tier.
- Finding a value inside a horizontal header row so you can pull the right column.
XMATCH syntax
=XMATCH(lookup_value, lookup_array, [match_mode], [search_mode])| Argument | Required? | What to enter |
|---|---|---|
lookup_value | Required | The value you want to find. It can be text, a number, a date, or a cell reference. Example: "North" or C2. |
lookup_array | Required | The single row or column to search. It must be one-dimensional. Example: A2:A10. |
match_mode | Optional | How Excel matches the value. 0 or omitted = exact match; -1 = exact or next smaller; 1 = exact or next larger; 2 = wildcard match. Example: 0. |
search_mode | Optional | Which direction to search. 1 or omitted = first to last; -1 = last to first; 2 = binary search ascending; -2 = binary search descending. Example: -1. |
What it returns: A number representing the position of the found value within lookup_array. If no match is found, XMATCH returns #N/A.
XMATCH examples
1 Find the position of a salesperson in a team list
Here you want the position of "Priya" in the Salesperson column. The formula uses exact match mode 0 and searches B2:B8. Priya is the fifth name, so the result is 5. You could use that number to pull her region or target from nearby columns.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | ID | Salesperson | Region | |||
| 2 | 101 | Alex | North | 5 | ||
| 3 | 102 | Maya | South | |||
| 4 | 103 | Sam | East | |||
| 5 | 104 | Nina | West | |||
| 6 | 105 | Priya | North | |||
| 7 | 106 | Omar | South | |||
| 8 | 107 | Lena | East | |||
| 9 |
2 Find a value in a horizontal header row
XMATCH works across rows as well as down columns. The formula searches the header row A1:F1 for "Apr" and returns 4 because April is the fourth month header. You can then use that number with INDEX to pull the April figure from another row.
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Jan | Feb | Mar | Apr | May | Jun | |
| 2 | 120 | 135 | 150 | 160 | 155 | 170 | |
| 3 | |||||||
| 4 | 4 | ||||||
| 5 |
Common XMATCH errors and how to fix them
| Error | Why it happens | How to fix it |
|---|---|---|
| #N/A | The lookup value was not found in lookup_array, often because of extra spaces, different case expectations, or a value stored as text instead of a number. | Check the spelling and remove extra spaces with TRIM. Make sure numbers are not stored as text. For partial matches, use match_mode 2 with wildcards such as "*key*". |
| #VALUE! | lookup_array is not a single row or column, or an argument has the wrong type. | Select exactly one row or one column, such as A2:A20 or B1:F1. Do not pass a two-dimensional block. |
| #N/A with match_mode 1 or -1 | Approximate match modes expect the lookup_array to be sorted in the correct order. Unsorted data can return the wrong position or an error. | Sort the lookup column ascending for match_mode 1 or descending for match_mode -1, or use exact match mode 0 instead. |
| #NAME? | The function name is misspelled or the version of Excel does not support XMATCH. | Check the spelling as XMATCH. XMATCH requires Microsoft 365 or Excel 2021 and later; use MATCH in older versions. |
Tips for using XMATCH
- Use exact match mode 0 unless you specifically need approximate matching; it is the safest choice for text lists.
- Search from the bottom with search_mode -1 to find the most recent entry in a log or transaction list.
- Combine XMATCH with INDEX to return a value from another column at the same position.
- Remember that XMATCH returns a position, not the value itself; wrap it in INDEX when you need the actual value.
- Use wildcard match mode 2 for partial text searches, for example
"*pro*"to find any product containing pro. - Keep lookup ranges as single rows or columns; if you need a two-dimensional lookup, use XLOOKUP instead.
Frequently asked questions
What is the difference between XMATCH and MATCH?
XMATCH is the modern replacement for MATCH. It defaults to exact matching, supports searching from the end of a list, and has clearer match and search mode options. MATCH is still available for older workbooks.
Does XMATCH return the value or the position?
XMATCH returns the position number of the value within the lookup array. To get the value itself, combine XMATCH with INDEX, for example =INDEX(B2:B10,XMATCH("Keyboard",A2:A10)).
Can XMATCH search from the bottom of a list?
Yes. Set the fourth argument, search_mode, to -1. Excel then searches from the last item to the first and returns the position of the last match.
Why does XMATCH give #N/A when the value is clearly there?
The most common causes are extra spaces, numbers stored as text, or a lookup array that does not include the row you expect. Use TRIM and check the data type of both the lookup value and the list.
Related functions
Every example on this page was recalculated by SheetSkill’s spreadsheet engine. XMATCH reference