XMATCH

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.