Dynamic Array Formulas And Spill Ranges: How To Use Them In Excel Tables

In this video, learn a couple ways to prevent #SPILL errors when using dynamic array formulas that produce spill ranges in Excel Tables.
You can download the example Excel files on the accompanying blog post to follow along:

In the video we look at how to use the TEXTJOIN and INDEX functions to return results from the FILTER function in either one cell or multiple columns. I also show how to use the COUNTIF function to determine how many columns you will need for the INDEX function. The INDEX function allows us to return a single result from the array or spill range that is returned.

These techniques will work for any formula that returns a spill range including UNIQUE, SORT, SORTBY, SEQUENCE, FILTER, etc.

Related Videos:
New Excel Features: Dynamic Array Functions & Formulas that Spill
Total Rows for Dynamic Array Formulas & Spill Ranges:
3 Ways to Combine Text in Excel - Formulas & Power Query:
Excel Tables Tutorial: Beginners Guide for Windows & Mac:

#MsExcel #ExcelCampus
00:00 Introduction
01:27 Text Join
02:44 Index

[patrickschardt]

I really hope they add spilled range behavior to tables, especially with BYROW, LAMBDA, and other new functions

This is a great work around for now

[erikalink]

Last week I encountered a similar problem when I was building a table (not an Excel-table) with calculations, using the FILTER-function. There came a few SPILL!-errors and in this particular case the solution was wrapping the FILTER-function inside SUM-function. It gave eveywhere ONE result and it was exactly what I needed. Thanks for your other ideas: very helpfull!

[AndreaUK]

Thank you! You have a great day too! You’re great ! Love your channel!🌹

[indzara]

Both methods are simple and effective. The TEXTJOIN method is a nice way to display multiple text values as in this scenario while the INDEX or other aggregations like MIN & MAX would allow choosing one value among the list. Thanks for sharing, Jon. Best wishes.

[KimHongTan]

Hi John, thanks for the tips. I actually wrap the FILTER function with TRANSPOSE … it works well for me 🙂

[jacquesdoyon]

Very good video, as usual. Next topic could be the following: since tables can't be used as "primary container" of a dynamic array function, I would like to know how to add custom columns beside a dynamic array results and make the formulas in these columns to adapt to the depth of the dynamic array, i.e. that these extras columns will add/remove rows according the dynamic array results.

[darrylmorgan]

I Just Learned Something New With These Great Tips..Thank You Jon :)

[chavelooo]

Thanks a lot, simple and clear and very helpful
😀😀

[rayzor]

Even though I didn't have a need for this yet, I have a strong feeling that I will probably need to do this for a customer at some point in my life. 😀

[wayneedmondson]

Nice Jon. Great tips! Thanks for sharing :)) Thumbs up!!

[nadermounir]

Awesome Solutions John :) you always bring unique ideas/ Solutions in your tutorial. Thank you for your hard work :)

[medher]

This was awsome man, Thanks,

Can we try to use TOROW with FILTER function to avoide INDEX funtion?

[ExcelOffTheGrid]

Great video.

I must admit, I had just applied the rule of ‘dynamic arrays don’t work with tables’. But clearly that’s not true if we can limit to 1 result.

Hmmm… 🤔… I wonder what we can use this for.

[sakthivel-kgsk]

Hi John, I request you to make video regarding effective use of excel on mobile version .

[PATRICKMAWUTORDOKLAH]

I am using Excel 2019, and it seems my Excel don't recognize the filter function, any help?

[askeijaz]

which video editing and screen recording software are you using? The video looks so clean.

[ythtrwb]

It was great. One solution is to use the difference in the number of columns in the INDEX function as follows, in which case there is no need to use the COUNTIF and MAX number], tblCustomers[Customer ID]=[@[Customer ID]]), COLUMN()-COLUMN([Name])), "")

[DWerner]

When working with league schedules, is there an easy way to pull all the Home Teams (with an "@" before their team name) from the week column, remove the @ symbol and spill the results into an array without blank spaces?

[Invertedzero]

Thanks for this useful video! I'm trying to pass through the results of a FILTER function to a COUNTIF, but all I'm getting is "There's a problem with this formula", despite the results of the FILTER appearing to be in the same format as a column range i.e {"1";"2";...}. I use the fx button and see it just returns a blank result, is there any reason why this happens or workarounds?

[IPedr]

The index with filter formula can return all values right?
So if the Vlookup only can return 1 value and Xlookup can return 2(with -1 in the order last to one) your example can return all values?
Now we dont need "for" and "if" with vba for multiple result!!?
Thanks, very good job!!