Exploring XMATCH

February 5, 2023

21 Views 0

SaveSavedRemoved 0

Exploring XMATCH

The XMATCH function will return the relative position of an item within a range or an array. We will firstly look at a basic XMATCH construction to return a result and then look at an example that includes XMATCH and INDEX together.

Constructing XMATCH (syntax)

The syntax for XMATCH is similar to the XLOOKUP syntax, and includes the following arguments: XMATCH(lookup_value,lookup_array,[match_mode],[search_mode]).

Let’s look at a simple example using just the XMATCH function:

Open the workbook named XMATCH.xlsx to follow along.
On the XMATCH worksheet, we have a list of training sessions and their respective codes in cells L2:M10. In cell L14, we have entered a code (SCWA003) to use as the lookup value from the table range, and return the relative position in the range in cell M14.
Click into cell M14 and then construct the following formula:

=XMATCH(L14,M2:M10,).
Note that we do not have to specify the [match_mode] or [search_mode]

arguments, as an exact match is the default option.
Press Enter to confirm and see the position of the item within the range.

Figure 10.21 – XMATCH syntax using a simple example

If we alter the Session Code in cell L14, it will dynamically update to the relative position in the list for that code.

Combining XMATCH and INDEX

The first part of the formula will reference the INDEX function. Remember that INDEX will return intersecting points between a row and column. We will be searching and returning the Session Code, based on the row number located in the Training column. As INDEX requires the row number argument, we will incorporate XMATCH to locate that dynamically.

Let’s run through the steps:

Make sure you have the XMATCH.xlsx workbook open.
On the XMATCH worksheet, locate cell E2. This is where we will build our formula to look up and match the codes from the corresponding table.
Type =INDEX( to start the formula construction.
The first argument of the INDEX function is to provide the array. Select the Session Code range, M2:M10 as this is where we will be returning our code from. Ensure that the range is absolute, meaning that it remains constant, and then add a comma to move to the next argument. Your formula should now display as follows:

=INDEX($M$2:$M$10,.

We will now introduce the XMATCH function as the INDEX is asking for the row number. We will use this function to look up the row number. We will look up the Session type using the values in the Training column.

6. Type XMATCH(F2,$L$2:$L$10,)).

Figure 10.22 – INDEX AND XMATCH formula construction

7. Press Enter to see the result and then double-click on the crosshair mouse pointer to the bottom-right of cell F2 to fill the contents down the column. You should now see the Session Codes relevant to the Training provided for each row of the dataset.

Figure 10.23 – Result of the INDEX and XMATCH formulas showing the relevant session codes per training session

You can get quite creative with XMATCH arguments to display, for instance, the last training date attended for a particular person. We will run through this in the next topic to locate the results from last to first using the search_mode argument.

Locating results using search_mode

This is achieved by providing the correct search_mode argument in order to execute the desired result. Selecting -1 for this argument will return the last training date for a particular individual:

Click to access the INDEXMATCH sheet.
Click into cell M2 as this is where we will construct the formula.
We will follow the same steps as in the previous example but will look up the surname of the individual and generate the last date of training attended. Our INDEX array will be dates in the Date Attended column. XMATCH will look up the surname of the individual from the Surname column as an exact match, and lastly, it will locate the last training date.

Enter the following formula: =INDEX(I2:I21,XMATCH(L2,B2:B21,,-1)).