Which formula would provide a dynamic array showing all columns of the items table where the category equals “sun”?

Dynamic Array Functions & Formulas Microsoft just announced a new feature for Excel that will change the way we work with formulas. The new dynamic array formulas allow us to return multiple results to a range of cells based on one formula. This is called the spill range, and I explain more about it below. Excel currently has 7 new dynamic array functions,with more on the way. We can use these to create a list of unique values (remove duplicates), sort a list, output a filtered range of data, and so much more. Plus, existing functions can utilize this same spill range functionality. Goodbye Ctrl+Shift+Enter The goal of this new functionality is to eventually replace array formulas that we input with Ctrl+Shift+Enter (CSE). Don't worry, that will be a long goodbye. CSE formulas are much more complex, and we usually have to guess at how many cells we need to copy them to. Here's a The image below shows a CSE array formula, enclosed in curly brackets, that can be used to create a list of unique values (remove duplicates). If you don't have the new functionality yet, checkout this post by my friend Dave at Exceljet on how to the Let's take a look at how much easier this will be with the new dynamic array function, UNIQUE. The UNIQUE Function in Excel With the new UNIQUE function you'll be able to create a list of unique values (remove duplicate entries) using a very simple formula. To create a list of unique values, you simply reference the range that contains duplicates in th...

Array formulas are powerful formulas that enable you to perform complex calculations that often can’t be done with standard worksheet functions. They are also referred to as "Ctrl-Shift-Enter" or "CSE" formulas, because you need to press Ctrl+Shift+Enter to enter them. You can use array formulas to do the seemingly impossible, such as • Count the number of characters in a range of cells. • Sum numbers that meet certain conditions, such as the lowest values in a range or numbers that fall between an upper and lower boundary. • Sum every nth value in a range of values. Note: If you have a current version of ENTER to confirm the formula as a dynamic array formula. Otherwise, the formula must be entered as a legacy array formula by first selecting the output range, entering the formula in the top-left-cell of the output range, and then pressing CTRL+SHIFT+ENTER to confirm it. Excel inserts curly brackets at the beginning and end of the formula for you. For more information on array formulas, see This type of array formula can simplify a worksheet model by replacing several different formulas with a single array formula. • Click the cell in which you want to enter the array formula. • Enter the formula that you want to use. Array formulas use standard formula syntax. They all begin with an equal sign (=), and you can use any of the built-in Excel functions in your array formulas. For example, this formula calculates the total value of an array of stock prices and shares, and pl...

I need to create a running product from a column of numbers (I could use a row, but a column is easier to demonstrate here.) The input might be any arbitrary array. In fact, in the application where I would deploy this, it will not be a range, but rather another dynamic array within a LAMBDA formula. Here is an example of the Input column of numbers and the desired Output from the formula: Inputs Expected Dynamic Array Output 10 10 8 80 3 240 4 960 5 4800 The formula would spill the results. There are lots of solutions for a running total, but I've found no solution for a running product. I have tried a few different approaches, including SUBTOTAL and AGGREGATE with no success. I have also built a number of approaches that get the result, but are hard-coded to a fixed number of rows. I need the formula to adapt to any arbitrarily sized number of rows. The following formula is the closest I have gotten so far. This LET formula delivers the result, but, as you can see is fixed to 5 rows: =LET( a, , v, SEQUENCE( ROWS(a) ), h, TRANSPOSE( v ), stagr, (v - h + 1) * (v >= h), m, IFERROR(INDEX( a, IF(stagr>0,stagr,-1), ), 1), almost, INDEX(m,v,h) * INDEX(m,v,h+1) * INDEX(m,v,h+2) * INDEX(m,v,h+3) * INDEX(m,v,h+4), result, INDEX( almost, , 1 ), result ) The arbitrary array of numbers input is placed in the variable a. The next step is to create some indexes that will be used to address these numbers: v is a sequence of vertical rows for each number in a and h is a the same sequence...

The key benefit of Dynamic Arrays is the ability to work with multiple values at the same time in a formula. This is a big upgrade and welcome change. Dynamic Arrays solve some really hard problems in Excel, and will fundamentally change the way worksheets are designed.Once you see how they work, you'll never want to go back. Availability Dynamic arrays and the new functions below areonly available New: New functions As part of the dynamic array update, Excel now includes 8 new functions which directly leverage dynamic arrays to solve problems that are traditionally hard to solve with conventional formulas.Click the links below for detailsand examples for each function: Function Purpose Filter data and return matching records Generate array of random numbers Generate array of sequential numbers Sort range by column Sort range by another range or array Extract unique values from a list or range Modern replacement for VLOOKUP Modern replacement forthe MATCH function Video: As of January 2023, many more new functions have now been released to take advantage of the dynamic array engine. The complete list of new functions is: Example Before we get into the details, let's look at a simple example. Below we are using the new single formula entered in E5: =UNIQUE(B5:B15) // return unique values in B5:B15 The result is a list of the fiveunique city names, which appear in E5:E9. Like all formulas, UNIQUE will update automatically when data changes.Below, Vancouver has replaced Portl...

After my recent To which I can only reply: guilty as charged. I have always loved the Anyway, the recent addition of Let’s start with something simple. In Excel, the …that is then loaded into the Excel Data Model (aka Power Pivot – although this works exactly the same if I use a Power BI dataset, Azure Analysis Services or SQL Server Analysis Services as my source): What you can then do is use the CubeSet function to create a set of all the products like so: =CUBESET("ThisWorkbookDataModel", "[Sales].[Product].[Product].MEMBERS", "Product Set") …and then use the CubeRankedMember function to put each individual item of the set into a cell. Here’s a simple example worksheet, first with the formulas showing and then the results: This example shows the fundamental problem that has always existed with CubeRankedMember though: in order to show all the items in a set you need to know how many items there are in advance, and populate as many cells with CubeRankedMember formulas as there are items. In this case see how the range B4:B6 contains the numbers 1, 2 and 3; these numbers are used in the formulas in the range C4:C6 to get the first, second and third items in the set. If a fourth product was added to the table, however, it would not appear automatically – you would have to add another cell with another CubeRankedMember formula in it manually. I’ve seen some workarounds but they’re a bit hacky and require you to know what the maximum possible number of items in a set could e...

In Excel 365, how can I select non-contiguous columns from a range using a single dynamic array formula? Consider data in columns A:E as follows: Entering =XLOOKUP(G1,A1:E1,A2:E11) into cell G2 returns the column labeled col2. But =XLOOKUP(G1:I1,A1:E1,A2:E11) returns the first entry of the those three columns instead of the entire columns. How can I understand this behavior? What's an alternative? I can similarly retrieve a single column but not a group of columns with INDEX/ XMATCH or FILTER: =INDEX(A2:E11,0,XMATCH(G1,A1:E1)) =FILTER(A2:E11,A1:E1=G1) Thanks for contributing an answer to Super User! • Please be sure to answer the question. Provide details and share your research! But avoid … • Asking for help, clarification, or responding to other answers. • Making statements based on opinion; back them up with references or personal experience. To learn more, see our

An array formula is a formula that can perform multiple calculations on one or more items in an array. You can think of an array as a row or column of values, or a combination of rows and columns of values. Array formulas can return either multiple results, or a single result. Beginning with the September 2018 update for Enter. Earlier, legacy array formulas require first selecting the entire output range, then confirming the formula with Ctrl+Shift+Enter. They’re commonly referred to as CSE formulas. You can use array formulas to perform complex tasks, such as: • Quickly create sample datasets. • Count the number of characters contained in a range of cells. • Sum only numbers that meet certain conditions, such as the lowest values in a range, or numbers that fall between an upper and lower boundary. • Sum every Nth value in a range of values. The following examples show you how to create multi-cell and single-cell array formulas. Where possible, we’ve included examples with some of the dynamic array functions, as well as existing array formulas entered as both dynamic and legacy arrays. Download our examples This exercise shows you how to use multi-cell and single-cell array formulas to calculate a set of sales figures. The first set of steps uses a multi-cell formula to calculate a set of subtotals. The second set uses a single-cell formula to calculate a grand total. • Multi-cell array formula • Here we're calculating Total Sales of coupes and sedans for each salesperso...

In September, 2018 we announced that Below is a list of functions that could return multi-cell ranges or arrays in what we refer to as pre-dynamic array Excel. If these functions were used in workbooks predating dynamic arrays, and returned a multi-cell range or array to the grid (or a function that did not expect them), then silent implicit intersection would have occurred. Dynamic array Excel indicates where implicit intersection could occur using the @ operator, and as a result, these functions may be prepended with an @ if they were originally authored in a pre-dynamic array version of Excel. Additionally, if authored on dynamic array Excel, these functions may appear as legacy array formulas in pre-dynamic array Excel unless prepended with @. • • • • • • • • • • • • • • • • • • • • • All Need more help? You can always ask an expert in the

With office 365 (Excel) it's possible to create dynamic tables, similar to what Power Query does, by using spill formulas. The advantage of dynamic tables over excel tables is that new rows and columns will automatically be created or removed. Setting up these tables is a bit of a chore. Below is what I do. If anyone else has experimented with this, please give me some pointers. Here is an example of a dynamic table without and with UDF's: In my example, there is the name of an existing excel table in B1 and a column header name in B2. The dynamic table (and formula) starts in A4. The formula for a dynamic table in A4. The table lists all unique values in an existing table Table's, Col and provides a count. There is a totals row at the bottom. I also have dynamic formatting to make it look like an excel table. Here is the Formula: =LET( Table, B1, Col, B2, EmptyList, "-", Header, CHOOSE(,"Total",IFERROR(SUM(Counts),EmptyList)), Range1,Header, Range2,ReturnArray, Range3,Totals, Rows1,ROWS(Range1),Rows2,ROWS(Range2),Rows3,ROWS(Range3),Cols1,COLUMNS(Range1), RowIndex,SEQUENCE(Rows1+Rows2+Rows3),ColIndex,SEQUENCE(1,Cols1), Range123,IF(RowIndex CriteriaExclude,"- None -"), FilterLess,FILTER(ColumnReturn,ColumnFilter CriteriaExclude,"- None -"), ListReturn, IF(CriteriaOperator = "=", FilterEqual, IF(CriteriaOperator = ">", FilterGreater, IF(CriteriaOperator = "", FilterNotEqual, "")))), Return, IF(OR(ISBLANK(TableFilter),ISBLANK(TableHeaderFilter),ISBLANK(CriteriaExclude),ISBLA...