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**

## Example

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

**Table: POs**

**Table: Invoices**

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

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.

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

## Single FILTER function

Let’s start by building a FILTER function:

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.

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).

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.

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.

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.

- The variable is called
- 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.

- The array is the spill range of

## 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:**

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

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.