Channel: Excel Campus - Jon

Category: Education

Tags: excelarrays not allowed in excel tablesdynamic array formulasspilled array formulasspillexcel campusspill rangesspill error in excel tablespill errorsdynamic spilled array formulas

Description: 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: excelcampus.com/functions/spill-range-error-excel-table 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 youtube.com/watch?v=1HF0UGMF070 Total Rows for Dynamic Array Formulas & Spill Ranges: youtube.com/watch?v=pTbRwr4flQ8 3 Ways to Combine Text in Excel - Formulas & Power Query: youtube.com/watch?v=kD1u7AehsAo Excel Tables Tutorial: Beginners Guide for Windows & Mac: youtube.com/watch?v=fJnJTff4Na8 #MsExcel #ExcelCampus