# XLOOKUP latest by date

=XLOOKUP(max,dates,results,,-1) // latest match by date

To get the latest match in a set of data *by date*, you can use XLOOKUP in approximate match mode by setting match_mode to -1. In the example shown, the formula in G5, copied down, is:

where **date** (C5:C15), **item** (B5:B15) and **price** (D5:D15) are named ranges.

*beta function*, available only through the Office Insiders program.

### How this formula works

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:

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.

## Excel Formula Training

Formulas are the key to getting things done in Excel. In this accelerated training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You'll also learn how to troubleshoot, trace errors, and fix problems. Instant access. See details here.