Xlookup Last Match

Explanation

By default, XLOOKUP will return the first match in a data set. However, XLOOKUP offers an optional argument called search_mode to control the order in which data is searched. Setting search mode to -1 causes XLOOKUP to search “last to first” or, in other words, search backwards through the data. You can see this option used in the example shown. The formula in G5, copied down, is:

=XLOOKUP(F5,item,price,"no match",0,-1)

The same formula without named ranges is:

=XLOOKUP(F5,$B$5:$B$15,$D$5:$D$15,"no match",0,-1)

XLOOKUP’s arguments are configured as follows:

• The lookup_value comes from cell F5
• The lookup_array is the named range item (B5:B15)
• The return_array is the named range price (D5:D15)
• The not_found argument is provided as “no match”
• The match_mode is set to 0 (exact match)
• The search_mode is set to -1 (last to first)

At each row, XLOOKUP looks for the item name in column F in B5:B15, starting at the bottom . When an exact match is found, the corresponding price in column D is returned. If no match is found, XLOOKUP will return #N/A.

Note: this example depends on data being sorted by date in ascending order. If data is unsorted, see this example .

Dynamic Array Formulas are available in Office 365 only.

Explanation

XLOOKUP offers several features that make it exceptionally good for more complicated lookups. In this example, we want the latest price for an item by date . If data were sorted by date in ascending order, this would be very straightforward . However, in this case, data is unsorted .

By default, XLOOKUP will return the first match in a data set. To get the last match , we can set the optional argument search_mode , to -1 to cause XLOOKUP to search “last to first”. However, we can’t use this approach here because there is no guarantee that the latest price for an item appears last.

Instead, we can set the optional argument match_mode to -1 to force an approximate match of “exact or next smallest”, and adjust the lookup value and lookup array as explained below. The formula in G5, copied down, is:

=XLOOKUP(MAX(date),(item=F5)*date,price,,-1)

Working through arguments one by one, the lookup_value is the largest (latest) date in the data:

MAX(date) // get max date value

The lookup_array is derived with a boolean logic expression:

(item=F5)*date

By comparing each item to the value in F5, “Belt”, we get an array of TRUE/FALSE values:

{TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;TRUE;FALSE;FALSE}

where TRUE values represent entries for “Belt”. This array acts like a filter. When it is multiplied by the values in the named range date , the TRUE/FALSE values are evaluated to 1’s and 0’s:

={1;0;0;0;0;0;1;0;1;0;0}*date

The result is an array that contains only zeros and dates for belts:

={43484;0;0;0;0;0;43561;0;43671;0;0}

Note: the serial numbers are valid Excel dates .

• This array is delivered directly to XLOOKUP as the lookup_array argument.
• The return_array is the named range price (D5:D15)
• The optional argument not_found is not provided.
• Match_mode is set to -1, for exact match, or next smallest item.

XLOOKUP looks through the lookup array for the maximum date value. Since the array has already been filtered to exclude dates not associated with “Belt”, XLOOKUP simply finds the best match (either the exact date, or the next smallest date) which corresponds to the latest date. The final result is the price associated with the latest date. The formula will continue to work when the data sorted in any order.