Dynamic Arrays 3

This is the third post in the Dynamic Arrays series. In the first two posts, we discussed the spill range and the spill reference operator. If you’ve enjoyed the first two posts, I think you’ll enjoy this one even more because we get to talk about the FILTER and XLOOKUP functions.

Video

Overview

So far, we’ve discovered that dynamic array formulas can return multiple results, and when they do, the results spill out into the adjacent cells. So far, the results have spilled down and our formulas have generated vertical lists. It is time to take another step and discover that formulas can return multiple rows and columns. Let’s start by looking at the FILTER function.

FILTER

The FILTER function starts with an input array and returns a filtered subset. Let’s say we have a data table Table1 like this:

And let’s say that we’d like to generate a list of all the Refunds on another worksheet.

We could use the FILTER function, like this:

=FILTER(Table1, Table1[Type]=“Refund”)

We hit Enter … and bam:

The FILTER function returns all the columns in the first argument (Table1) and includes the matching rows that meet the criteria defined in the second argument (Table1[Type]= “Refund”).

In other words, it returns an array that contains multiple rows and columns!

And, as you might suspect, if the source data changes the dynamic array formula will update the spill range accordingly.

Let’s take a look at one more function, XLOOKUP.

XLOOKUP

The XLOOKUP function is designed as an alternative to VLOOKUP and includes many benefits.

One benefit is that in a single formula, it can return all columns for the matching value whereas VLOOKUP returns the value from a single column.

Let’s say we have a bunch of transactions, like this:

We have the AcctNum for each transaction, but we need the AcctName and FS line. These are stored in a lookup table, Table1, which looks a bit like this:

We only need one XLOOKUP function to retrieve both AcctName and FS column values. If you haven’t explored XLOOKUP, I’ve written several times about it here, but in general, you define the lookup value, the lookup column, and the return column or columns. So, we could write the following formula into E9:

=XLOOKUP(C9,Table3[AcctNum],Table3[[AcctName]:[FS]])

We hit Enter … and bam:

It returns both the AcctName and FS columns!

We can fill the formula down … and … got it:

Note: since the AcctNum isn’t a spill range, using C9# reference doesn’t fill the formula down automatically.

As you can tell, dynamic arrays offer a new way to approach certain tasks.

These three posts have provided a warm-up, but there are many additional functions and uses to explore. If you’d like to dig into a few additional functions and applications, check out these posts.

What did you think about this series? Post a comment below and let me know…thanks!

Sample File