Dave Bruns

In this example, the goal is to compare the values in two different lists, then extract the values that appear in both lists into a third list as shown in the worksheet above. The values for List 1 appear in column B, and the values for List 2 appear in column D. Although we have a list of...Read more

This formula uses XLOOKUP twice, by nesting one XLOOKUP inside another. The first (inner) XLOOKUP is used to perform an exact match lookup on the value in G5:

XLOOKUP(G5,E5:E15,B5:E15)

• The
...Read more

Working from the inside out, XLOOKUP is configured to find the value in H4 in the Last name column, and return all fields. In order to support wildcards, match_mode is provided as 2:

XLOOKUP(H4,D5:D15,B5:E15,2) //...Read more

This formula is based on the XLOOKUP function. Working from the inside out, we use the MAX function to calculate a lookup value:

MAX(values)

MAX is...Read more

This example is based on the formula explained in detail here:

=ROW()-INDEX(ROW(data),1,1)+1>ROWS(data)-n

The formula uses the greater than operator (>) to...Read more

One of XLOOKUP's features is the ability to lookup and return an entire row or column. This feature can be used to nest one XLOOKUP inside another to perform a two-way lookup. The inner XLOOKUP returns a result to the outer XLOOKUP, which returns a final result...Read more

The core of this formula is based on another formula that calculates the "current row" in a range:

=ROW()-ROW(rng.firstcell)+1

In brief, we get the current row in the workbook, then subtract the...Read more

NETWORKDAYS is a built-in function accepts a start date, an end date, and (optionally) a range that contains holiday dates. In the example shown, we generate the start and end date using the DATE function like this:

...Read more

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....Read more

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...Read more