XMATCH function in Excel

Introduction

In this article, we will understand the concept of the XMATCH function which is simple and easy to use. The XMATCH function is exclusive only to the latest version like Excel 365 or Excel 2021. It would retrieve the specified position of the data in the array. It is limited to utilizing a single row or column but not compatible with multiple columns or multiple columns. Manually finding the position of the items in lengthy datasets is very time-consuming and tedious.

XMATCH function in Excel

Step 1

Open a new worksheet in Excel 365 and create two tables with the corresponding entries as depicted in the below image. Our main task is to find the relative index of the Employee ‘Daniel’.

Step 2

Enter the formula “=XMATCH(F2,A2:A7)” in the E3 cell and then press the Enter tab.

INDEX function with XMATCH function

Step 1

Assume the sample dataset as shown below image −

Step 2

Now, we must identify the relative position of the employee’s name. In the F2 cell, we have written the employee name “Dennis”. To find its employee’s relative position, write the formula =XMATCH(F2,Table2[Employee Name]) in the F3 cell as highlighted in below image−

Step 3

Press the “Enter” tab to extract the position number. Now, you need to find out the incentive of the Dennis employee whose relative position is 4. Write the formula =INDEX(Table2[Incentive],XMATCH(F2,Table2[Employee Name])) in the F4 cell as shown below −

Explanation

INDEX(Table2[Incentive],XMATCH(F2,Table2[Employee Name]))

• Table2[Incentive]− The first argument selects the range C2−C7 indirectly.

• XMATCH(F2,Table2[Employee Name]))− The second argument defines the inner XMATCH function to get the position of the data defined in the F2 cell.

Step 4

Therefore, you will obtain the incentive ‘1000’ of the Dennis in the F4 cell as highlighted in the below image. Similarly, you can change the employee’s name in the F2 cell and retrieve its incentive as defined in the given table.

Conclusion

Users may enhance their Excel skills using these interesting methods that are provided in both examples. The users must write the XMATCH formula carefully to get an accurate value, otherwise errors may arise.