Explanation
The XMATCH function offers new features not available with the MATCH function. One of these is the ability to perform a “reverse search”, by setting the optional search mode argument. The default value for search mode is 1, which specifies a normal “first to last” search. In this mode, XMATCH will match the lookup value against the lookup array, beginning at the first value.
=XMATCH(F5,names,0,1) // start with first name
Setting search mode to -1 species a “last to first” search. In this mode, XMATCH will match the lookup value against the lookup array, starting with the last value, and moving toward the first:
=XMATCH(F5,names,0,-1) // start with last name
Retrieve date and amount
XMATCH returns a position. Typically, XMATCH is used with the INDEX function to return a value at that position. In the example show, we can use INDEX and XMATCH together to retrieve the date and sales for each name as follows:
=INDEX(dates,XMATCH(F5,names,0,-1)) // get date
=INDEX(sales,XMATCH(F5,names,0,-1)) // get sale
where dates (C5:C15) and sales (D5:D15) are named ranges . As before, search mode is set to -1 to force a reverse search.
For more information about using INDEX with MATCH, see How to use INDEX and MATCH .
Dynamic Array Formulas are available in Office 365 only.
Explanation
The goal is to match a row in a set of data based on a given Item, Size, and Color. At a glance, this seems like a difficult problem because XMATCH only has one value for lookup_value and lookup_array . How can we configure XMATCH to consider values from multiple columns? The trick is to generate the lookup array we need using Boolean logic, then configure XMATCH to look for the number 1. This approach is explained below.
The XMATCH function
The XMATCH function is an upgraded replacement for the older MATCH function . At the core, XMATCH performs a lookup and returns the numeric position of the lookup value in a range or array as the result. XMATCH performs an exact match by default and in its simplest form, requires just two arguments , a lookup value and a lookup array:
=XMATCH(lookup_value,lookup_array)
Looking at the generic syntax above, you can see there is no obvious way to provide multiple criteria.
Boolean Logic
This formula works around this limitation by using Boolean logic to create a temporary array of ones and zeros to represent rows matching all 3 criteria, then asking XMATCH to find the first 1 in the array. The temporary array of ones and zeros is generated like this:
(B5:B15=H5)*(C5:C15=H6)*(D5:D15=H7)
This code compares the Item entered in H5 with all items, the Size in H6 with all sizes, and the Color in H7 with all colors. Each expression generates an array of TRUE and FALSE values as seen below:
{FALSE;FALSE;FALSE;FALSE;TRUE;TRUE;TRUE;FALSE;FALSE;FALSE;FALSE}*{FALSE;TRUE;FALSE;FALSE;FALSE;TRUE;FALSE;TRUE;TRUE;FALSE;FALSE}*{FALSE;TRUE;FALSE;TRUE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE}
Tip: use F9 to see these results . Just select an expression in the formula bar, and press F9.
Next, the math operation (multiplication) automatically converts the TRUE and FALSE values to 1s and 0s:
{0;0;0;0;1;1;1;0;0;0;0}*{0;1;0;0;0;1;0;1;1;0;0}*{0;1;0;1;0;1;0;0;0;0;0}
This behavior is a basic feature of how Excel works with Boolean values . After the arrays are multiplied together, we have just a single array of 1s and 0s like this:
{0;0;0;0;0;1;0;0;0;0;0}
Notice the 6th value in the array is 1. This represents the one row in the data that meets all three conditions, since this row contains a Medium Blue Hoodie. This is the array used inside XMATCH as the lookup_array . Because the lookup_array contains only 1s and 0s, we provide 1 as the lookup_value . At this point, we can rewrite the formula like this:
=XMATCH(1,{0;0;0;0;0;1;0;0;0;0;0}) // returns 6
XMATCH matches the 1 in the sixth row of the array and returns 6 as a final result.
Visualizing arrays
The arrays explained above can be challenging to understand since they aren’t visible when the formula calculates. The screen below shows how the arrays can be visualized with simple formulas in a worksheet. Columns B, C, and D correspond to the data in the example when the logical expressions for “Hoodie”, “Medium”, and “Blue” are being evaluated." Column F is the result of multiplying the ranges together:
The result in F5:F15 represents the array delivered to XMATCH as the lookup_array .
INDEX and XMATCH
Typically, the XMATCH function is used in an INDEX and MATCH formula to return a value instead of a position. In the original worksheet above, the formula in cell H9 uses INDEX with XMATCH to retrieve the correct price for a Medium Blue Hoodie:
=INDEX(E5:E15,XMATCH(1,(B5:B15=H5)*(C5:C15=H6)*(D5:D15=H7)))
Notes
- The XLOOKUP function can also be configured to use multiple criteria in the same way.
- This approach can be adapted to apply more complex criteria .
