Dave Bruns

The first thing this formula does is check the date in column D against the date in the header (E4:J4).

=IF($D5<=E$4

Translated: if the date in column D is less than or equal to the date in row E.

Note these are...Read more

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

When you are working with a large table that extends beyond the visible area of a worksheet, you might want to see the most recent data in the table without scrolling to the bottom of the table. Examples of this kind of data include things like:

• Recent deposits or
...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