XMATCH

Back to all functions

XMATCH is a modernised version of Excel’s MATCH function, which returns the relative position of a given value within a range of cells.

The difference is that XMATCH is slightly more adaptable and stable, and offers some extra functionality.

We have a staff list, sorted first by department, and then in descending order by gross salary, and we want to see who in each department earns the least.

We’ll select the cell to the right of Management and enter =XMATCH AND left parenthesis.

The first argument is the lookup value, where we refer to cell H6.

The second argument is the range of cells that we will search through, which for us will be the departments.

We make this reference absolute as we will be copying the formula to each department.

The third argument is the match mode, i.e. whether we want an exact match, the next smallest value, or the next largest value.

We want to find our department, so we enter a zero for an exact match.

The fourth argument is search mode, where we can specify whether we want the function to search from first to last, last to first, or use a so-called binary search, which we will skip over for the time being.

Searching from last to first is one of XMATCH’s new features, and since we want to see who earns the least, we’ll use it by entering -1.

We finish the function, hit enter, and see 8 in the cell.

This means that the person who was first found in the reverse search, which thanks to our sorting is the one who earns the least, is in position 8.

If we copy the function downwards, we will see the positions of each department.

To now find the name of the person that earns the least, we’ll undo the copying, and create an INDEX function.

As the first argument, we enter the Names and make the reference absolute, and then make the XMATCH function the second arguments.

We’ll finish the function with a right parenthesis, hit enter, and we now have the name of the person in each department who earns the least in gross salary.

Popular courses

The Excel Functions Guide

Deepen and develop your Excel Functions skills

Learn how to efficiently manage databases, clear text in cells, and retrieve values from other tables. The course also walks...

Excel Essentials

Get a better understanding of the Excel workflow

Learn to feel more comfortable in Excel. This course will help quickly develop your knowledge in Excel. Excel Essentials is...

Contact us for assistance in finding right course

Excel for your profession

The Excel course for Procurement and Logistic professionals

Excel for professionals working within procurement and logisticians

In cooperation with organisations and professionals within the industry, Learnesy have developed an Excel course that is adapted to those...

Looking for something custom made? Contact us and we will make sure to meet your requirements.

Contact