How to use the XMATCH function

What is the XMATCH function?

The XMATCH function searches for a given value in a range of cells and then returns the values's relative position in the range. The XMATCH function is an enhanced version of the MATCH function, it also has better default settings not prone to errors.

1. Introduction

When was the XMATCH function introduced in Excel?

XMATCH became available in Excel 365 in September 2019, it is not available in older standalone versions of Excel like Excel 2016 or 2019. It requires a Microsoft 365 subscription or Excel 2021/2022 to use this function.

The XMATCH function is an improved version of the MATCH function, the latter function has been a core function in Excel for decades.

Is the XMATCH function an Excel 365 dynamic array function?

The XMATCH function is designed to return a single value, however, it is possible to make it return multiple values simply by having more values in the lookup_value argument which is the first argument in the XMATCH function. It is also important to note that though the function does return multiple values, it only returns the relative position of the first instance it finds based on each lookup_value.

What is an Excel 365 dynamic formula?

An Excel 365 dynamic array formula is a powerful new feature that allows a single formula to return multiple results across a range of cells automatically. Dynamic array formulas represent a significant change in Excel's functionality allowing for more efficient and flexible spreadsheet design and calculations.

Here are the important points of Excel 365 dynamic array formulas:

• Single cell entry: The formula is entered in just one cell, but can output results to multiple cells automatically, this is called spilling.
• Automatic spilling: Results "spill" into adjacent cells automatically without needing to copy the formula in contrast to older Excel versions.
• Dynamic resizing: The output destination range instantly and automatically adjusts as the source data changes.
• No special keystrokes: Unlike traditional array formulas, dynamic arrays don't require Ctrl+Shift+Enter.
• New functions: Excel 365 introduced several new functions designed to work with dynamic arrays, like FILTER, SORT, UNIQUE, etc.
• Works with existing functions: Most existing Excel functions now support dynamic array behavior.
• Easier complex calculations: Dynamic arrays simplify tasks that were previously difficult, like filtering data.
• Backward compatibility: Dynamic arrays are only available in Microsoft 365 versions of Excel, not in older versions like Excel 2019 or 2016.

 

2. Excel Function Syntax

XMATCH(lookup_value, lookup_array, [match_mode], [search_mode])

lookup_value	Required. A cell reference or a constant to a lookup value.
lookup_array	Required. A cell reference or an array to search.
[match_mode	Optional. Match setting: 0 - Exact match (default value). The XMATCH function returns a #N/A error if an exact match is not found. -1 - Exact match or next smallest item. 1 - Exact match or next largest item 2 - Characters * ? and ~ can be used to perform a wildcard match.
[search_mode]	Optional. Search setting: 1 - Search first-to-last (default), the XLOOKUP function searches from top to bottom or left to right. -1 - Search last-to-first (reverse search), , the XLOOKUP function searches from bottom to top or right to left. 2 - Binary search, lookup_array must be sorted in ascending order. -2 - Binary search, lookup_array must be sorted in descending order.

Back to top

3. Example 1

The XMATCH function returns the lookup values relative position in a cell range or array. It matches only the first found instance from top to bottom.

Formula in cell D3:

The XMATCH function in cell E6 uses the value in cell E3 as a lookup value, the lookup array is cell range C3:C8. The result is 4, the lookup value is the fourth value in cell range C3:C8.

Back to top

4. How to return the relative positions of all matching lookup values

This is an Excel 365 dynamic array formula, it contains the FILTER and SEQUENCE functions that exist only in Excel 365.

The image above demonstrates a formula that returns the relative positions of all found instances. For example, lookup value specified in cell E3 is found twice in cell range C3:C8.

The corresponding relative position is 4 and 6, see gray numbers adjacent to cell range C3:C8.

Formula in cell E6:

Back to top

4.1 Explaining formula in cell E6

Step 1 - Count values in cell range

The ROWS function counts the number of rows in a cell range or array.

ROWS(C3:C8)

returns 6. There are six rows in cell range C3:C8.

Step 2 - Create a sequence

The SEQUENCE function returns an array containing sequence from 1 to n.

SEQUENCE(ROWS(C3:C8))

becomes

SEQUENCE(6)

and returns {1; 2; 3; 4; 5; 6}.

Step 3 - Logical test

This step creates a logical expression that will filter corresponding numbers in the next step (step 4).

The equal sign is a logical operator and the output is a boolean value TRUE or FALSE. It compares all values in cell range C3:C8 to value in cell E3.

C3:C8=E3

becomes

{"Train"; "Bus"; "Bike"; "Car"; "Boat"; "Car"}="Car"

and returns

{FALSE; FALSE; FALSE; TRUE; FALSE; TRUE}.

Step 4 - Filter numbers based on the lookup value

The FILTER function filters values based on a logical expression.

FILTER(SEQUENCE(ROWS(C3:C8)),C3:C8=E3)

becomes

FILTER({1; 2; 3; 4; 5; 6},{FALSE; FALSE; FALSE; TRUE; FALSE; TRUE})

and returns {4; 6}.

Back to top

5. How to calculate the relative position of column and row

This formula calculates the relative position of a matching lookup value in a multi-column cell range.

The image above demonstrates a formula in cell I4 that calculates the relative column number based on the specified lookup value in cell I2.

There is also a second formula in cell I5 that calculates the relative row number.

Formula in cell I4:

Formula in cell I5:

Explaining formula in cell I4

Step 1 - Count columns in cell reference

COLUMNS(B3:F8)

Step 2 - Create a sequence

SEQUENCE(, COLUMNS(B3:F8))

Step 3 - Match value

I2=B3:F8

Step 4 - Get the relative position

IF(I2=B3:F8, SEQUENCE(ROWS(B3:F8)), "")

Step 5 - Extract smallest number in array

MIN(IF(I2=B3:F8, SEQUENCE(, COLUMNS(B3:F8)), ""))

Back to top

6. Search last to first

This formula demonstrates how to perform a lookup last to first using the XMATCH function.

Formula in cell E6:

The last argument in the XMATCH function lets you specify search mode. -1 tells the function to perform a last to first search.

XMATCH(lookup_value, lookup_array, [match_mode], [search_mode])

Back to top

7. Search right to left

This formula demonstrates how to perform a lookup right to left using the XMATCH function.

Formula in cell B9:

The last argument in the XMATCH function lets you specify search mode. -1 tells the function to perform a last to first search, or right to left.

XMATCH(lookup_value, lookup_array, [match_mode], [search_mode])

Back to top

8. Function not working

The image above shows a common error scenario with the XMATCH function, it returns a #N/A error. The function looks for the value "z" displayed in cell B2 in cell range D2:D6 but can't find it. #N/A means not available.

8.1 Understanding the error values

When you encounter an error value in a cell a warning symbol appears. Press with mouse on it to see a pop-up menu that lets you get more information about the error.

1. The first line describes the error.
2. The second line opens a pane that explains the error in greater detail.
3. The third line takes you to the "Evaluate Formula" tool, a dialog box appears allowing you to examine the formula in greater detail.
4. This line lets you ignore the error value meaning the warning icon disappears, however, the error is still in the cell.
5. The fifth line lets you edit the formula in the Formula bar.
6. The sixth line opens the Excel settings so you can adjust the Error Checking Options.

Here are a few of the most common Excel errors you may encounter.

• #NULL error - This error occurs most often if you by mistake use a space character in a formula where it shouldn't be. Excel interprets a space character as an intersection operator. If the ranges don't intersect an #NULL error is returned. The #NULL! error occurs when a formula attempts to calculate the intersection of two ranges that do not actually intersect. This can happen when the wrong range operator is used in the formula, or when the intersection operator (represented by a space character) is used between two ranges that do not overlap. To fix this error double check that the ranges referenced in the formula that use the intersection operator actually have cells in common.
• #SPILL error - The #SPILL! error occurs only in version Excel 365 and is caused by a dynamic array being to large, meaning there are cells below and/or to the right that are not empty. This prevents the dynamic array formula expanding into new empty cells.
• #DIV/0 error - This error happens if you try to divide a number by 0 (zero) or a value that equates to zero which is not possible mathematically. Use the "Evaluate formula" tool to pinpoint the exact location in the formula where this error occurs. The "Evaluate formula" tool is located on the "Formulas" tab on the ribbon. Select the cell containing the #DIV/0 error and then press with left mouse button on the "Evaluate formula button".
• #VALUE error - The #VALUE error occurs when a formula has a value that is of the wrong data type. Such as text where a number is expected or when dates are evaluated as text.
• #REF error - The #REF error happens when a cell reference is invalid. This can happen if a cell is deleted that is referenced by a formula.
• #NAME error - The #NAME error happens if you misspelled a function or a named range.
• #NUM error - The #NUM error shows up when you try to use invalid numeric values in formulas, like square root of a negative number.
• #N/A error - The #N/A error happens when a value is not available for a formula or found in a given cell range, for example in the VLOOKUP or MATCH functions.
• #GETTING_DATA error - The #GETTING_DATA error shows while external sources are loading, this can indicate a delay in fetching the data or that the external source is unavailable right now.

8.2 The formula returns an unexpected value

To understand why a formula returns an unexpected value we need to examine the calculations steps in detail. Luckily, Excel has a tool that allows for a more detailed analysis of a formula.

Here is how to troubleshoot a formula:

1. Select the cell containing the formula you want to examine in detail.
2. Go to tab “Formulas” on the ribbon.
3. Press with left mouse button on "Evaluate Formula" button. A dialog box appears.
   The formula appears in a white field inside the dialog box. Underlined expressions are calculations being processed in the next step. Italicized expressions are the most recent result. Four buttons at the bottom of the dialog box allows you to evaluate the formula in smaller steps based on intermediate calculations which you control.
4. Press with left mouse button on the "Evaluate" button located at the bottom of the dialog box to process the underlined expression.
5. Repeat press with left mouse button oning the "Evaluate" button until you have seen all calculations step by step. This allows you to examine the formula in greater detail and hopefully find the culprit.
6. Press "Close" button to dismiss the dialog box.

There is also another way to debug formulas using the function key F9, demonstrated in the images above. F9 is especially useful if you have a hunch that a specific part of the formula is the issue, this makes it faster than the "Evaluate Formula" tool since you don't need to go through all calculations to find the problem.

1. Enter Edit mode: Double-press with left mouse button on the cell or press F2 to enter Edit mode for the formula.
2. Select part of the formula: Highlight the specific part of the formula you want to evaluate. You can select and evaluate any part of the formula that could work as a standalone formula.
3. Press F9: This will calculate and display the result of just that selected portion.
4. Evaluate step-by-step: You can select and evaluate different parts of the formula to see intermediate results.
5. Check for errors: This allows you to pinpoint which part of a complex formula may be causing an error.

Tips!

• View actual values: Selecting a cell reference and pressing F9 will show the actual values in those cells.
• Exit safely: Press Esc to exit Edit mode without changing the formula. Don't press Enter, as that would replace the formula part with the calculated value.
• Full recalculation: Pressing F9 outside of Edit mode will recalculate all formulas in the workbook.

Remember to be careful not to accidentally overwrite parts of your formula when using F9. Always exit with Esc rather than Enter to preserve the original formula. However, if you make a mistake overwriting the formula it is not the end of the world. You can “undo” the action by pressing keyboard shortcut keys CTRL + z or pressing the “Undo” button

8.3 Other errors

Floating-point arithmetic may give inaccurate results in Excel - Article

Floating-point errors are usually very small, often beyond the 15th decimal place, and in most cases don't affect calculations significantly.

Useful links

XMATCH function - Microsoft
Excel XMATCH function with formula examples

Functions in 'Lookup and reference' category

The XMATCH function function is one of 25 functions in the 'Lookup and reference' category.

Excel function categories

Excel categories

Leave a Comment