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:

A screenshot of an excel table with amount and type columns

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:

A screenshot of an excel worksheet filtered to show just the refund rows

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:

A screenshot of an excel worksheet with transactions that include account number and amount

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:

A screenshot of an excel table that stores account number, account name, and fs columns

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:

A screenshot of an excel worksheet with the xlookup function returning both the acctname and fs values

It returns both the AcctName and FS columns!

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

A screenshot of an excel worksheet that contains the acctname and fs for all rows (the xlookup formula was filled down)

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

Posted in , ,

Jeff Lenning

I love sharing the things I've learned about Excel, and I built Excel University to help me do that. My motto is: Learn Excel. Work Faster.

Stay Connected

If you'd like to be notified when I write a new Excel article, enter your name and email and click SUBSCRIBE. You can unsubscribe anytime, and I will never sell your email address.

Want to learn Excel?

Our Campus Pass includes access to our entire Undergrad and Masters catalog. Gamification ensures it is the most fun you can have learning Excel :)

Leave a Comment





For security, use of Google's reCAPTCHA service is required which is subject to the Google Privacy Policy and Terms of Use.

I agree to these terms.