How to spill multiple FILTER functions in Excel

In a recent project, I used FILTER and TEXTJOIN to create a comma-separated list to display in a cell. Unfortunately, when I tried to spill the values for different filter criteria, it created an error. So, in this post, we will work through a solution, showing how to spill multiple FILTER functions in Excel.

Table of Contents

Download the example file: Click the button below to join the Insiders program and gain access to the example file used for this post.

File name: 0187 Spill multiple FILTER functions.zip

Watch the video

How to Spill multiple FILTER functions in Excel | Excel Off The Grid

Watch the video on YouTube

Example

In the example workbook, there are two Tables: POs (Purchase Orders) and Invoices:

Table: POs

POs Table for Example

Table: Invoices

Invoices Table for Example

From these Tables, there is a summary showing the PO value, Invoice value, and Variance for each PO.

Calculations including dynamic arrays for example

Cell I2 contains a dynamic array formula to display the PO Numbers from the POs Table in alphabetical order.

=SORT(UNIQUE(POs[PO Number]))

NOTE:

To ensure we have a complete list of purchase orders (POs) from both Tables, we could use the following formula.

=SORT(UNIQUE(VSTACK(POs[PO Number],Invoices[PO Number])))

Cell J2 contains a formula to display the total value for each PO.

=SUMIFS(POs[Value],POs[PO Number],I2#)

Cell K2 contains a formula to display the amount invoiced per PO.

=SUMIFS(Invoices[Value],Invoices[PO Number],I2#)

Finally, cell L2 calculates the variance between the PO Value and Invoice Value.

=J2#-K2#

These are dynamic array formulas; if new purchase order numbers are added to the POs table, the report updates automatically.

The goal is to list the invoice numbers relating to the PO, as shown in cell M2 below.

Required output - Spill Multiple FILTER

It should be pretty easy… right?… or maybe not.

Single FILTER function

Let’s start by building a FILTER function:

FILTER with Spill range

Cell M2 contains the following formula:

=FILTER(Invoices[Invoice Number],Invoices[PO Number]=I2)

The spill range returned by the FILTER contains the two invoice numbers that match PO0001.

TIP:

To learn more about the FILTER function, check out this post: FILTER function in Excel.

Now, let’s wrap FILTER in a TEXTJOIN to get a comma-separated list of invoices in a single cell.

FILTER and TEXTJOIN in single cell

The formula in cell M2 is:

=TEXTJOIN(", ",TRUE,
FILTER(Invoices[Invoice Number],Invoices[PO Number]=I2)
)

This gives us the invoices in a comma-separated list.

So far, so good.

The problem with multiple FILTER functions

So now, to get the FILTER to calculate for each PO Number, surely we only need to change I2 to I2# (the spill range of the PO numbers).

Multiple FILTER functions lead to error

The formula in cell M2 is:

=TEXTJOIN(", ",TRUE,
FILTER(Invoices[Invoice Number],Invoices[PO Number]=I2#)
)

Unfortunately, that returns the N/A# error. That’s not what we want! 🤔

While FILTER is a dynamic array function that spills filter results, it cannot spill multiple FILTER calculations.

Multiple FILTER functions inside a Table

In our example, the TEXTJOIN/FILTER combination returns a single value. This means we can use it inside a Table without creating a #SPILL! error.

So, if we want to include the Invoice List inside the POs table, we simply include the TEXTJOIN/FILTER formula in the table column.

Multiple Filter functions inside a Table

The formula in cell C2 is:

=TEXTJOIN(", ",TRUE,FILTER(Invoices[Invoice Number],Invoices[PO Number]=[@[PO Number]]))

The calculated column feature of Tables performs a separate calculation for each row. It is not a spill range.

Simple enough. So, if this works for Tables, how can we make this work for standard ranges?

BYROW & LAMBDA to spill multiple FILTER functions

Just as a table column performs individual calculations for each row, the BYROW/LAMBDA combination performs separate calculations for each row in an array.

Multiple Filter functions with BYROW and LAMBDA

The formula in Cell M2 is:

=BYROW(I2#,
LAMBDA(r,
TEXTJOIN(", ",TRUE,
FILTER(Invoices[Invoice Number],Invoices[PO Number]=r)
)
))

This provides the result we want.

  • LAMBDA takes variables and uses them in a function we define.
    In this scenario:
    • The variable is called r.
    • The TEXTJOIN/FILTER combination is the defined function.
    • r is used as the variable inside the TEXTJOIN/FILTER combination.
  • BYROW tells LAMBDA to perform the calculation for each row in an array.
    In this scenario:
    • The array is the spill range of I2#
    • Each row in the array is passed into the LAMBDA to use as the r variable.

Alternative approach for stacking multiple FILTER functions

If you’re thinking: “But I haven’t aggregated FILTER down to a single value, this won’t work for me…”

There is an alternative DROP/REDUCE/LAMBDA/VSTACK approach we could use.

=DROP(
REDUCE(1,I2#,
LAMBDA(s,c,VSTACK(s,
TEXTJOIN(", ",TRUE,
FILTER(Invoices[Invoice Number],Invoices[PO Number]=c)
)
)))
,1)

This approach appends the result of each FILTER function using the VSTACK function; then it spills the final result.

This method works when FILTER returns multiple values, rather than a single value. This is more than we need for our example, but it may be what you need for your scenario.

Conclusion

In this example, we have seen:

  • How to aggregate the values of a FILTER function into a string with TEXTJOIN.
  • A calculated column performs a calculation for each row in the Table.
  • Tables return dynamic arrays where the formula returns a single value.
  • The BYROW/LAMBDA combination performs a calculation for each row of a spill range.

It just goes to prove, sometimes simple scenarios can be a little trickier than we might expect.

Related Posts:

2 thoughts on “How to spill multiple FILTER functions in Excel”

  1. Nice writeup. BYROW and REDUCE are probably my two favorite LAMBDA helpers, and pretty much the exact kinds of scenarios you described!

    Reply
    • They both take a bit of time to understand. But incredibly powerful.

      I have found that using a SQEUENCE inside the REDUCE array means you can loop through virtually anything.

      Reply

Leave a Comment