Excel Array Revelation: Excel Won't Support an Array of Arrays - Episode 2314

Microsoft Excel Tutorial: Why won't Excel support an array of arrays?

Welcome back to MrExcel Netcast! In this episode, we're going to talk about a limitation of Excel when it comes to dynamic arrays. As more and more people are starting to use dynamic arrays, a common question keeps popping up on the boards - why won't Excel support an array of arrays? Well, let's dive into it and find out.

Thanks to dynamic arrays, we can now do some pretty amazing things, like using a single formula to return multiple results for a VLOOKUP. And with XLOOKUP, we can even return entire sets of columns at once. But here's the catch - while we can do all of these lookups and returns at once, we can't do the same for an array of values. It seems like a natural thing to want to do - look up multiple values and return multiple results - but unfortunately, it's not supported in Excel at the moment.

Joe McDaid, a fellow Excel expert, refers to this limitation as an "array of arrays". And while he's not against the idea, he just hasn't had the time to fix it yet. So for now, in February 2020, we are not allowed to use an array of arrays in Excel. This means that even though we can do vertical and horizontal lookups and returns, we can't do both at the same time. It's a bit frustrating, but it's important to be aware of this limitation when working with dynamic arrays.

I personally love dynamic arrays and have been incorporating them into my work more and more. But there are times when I wish I could extend them in another direction, only to be reminded that it's not possible yet. So just keep in mind that while dynamic arrays are a powerful tool, there are still some limitations to be aware of. And as always, if you have any questions or comments, feel free to leave them down below in the YouTube comments. And don't forget to hit that Subscribe button and ring the bell to stay updated on all of our latest netcasts from MrExcel. Thanks for watching!

#excel
#microsoft
#exceltutorial
#microsoftexcel
#microsoft365
#excelformula
#excelformulasandfunctions
#excelvlookup
#excelbug

This video answers these common search terms:
how do arrays work in excel
how to make arrays in formula in excel
how to use arrays in excel
what are arrays in excel
how to use dynamic array in excel
how to make a dynamic array formula in excel
what is excel dynamic array
what are excel dynamic array functions
why can't xlookup return all rows and all columns
what is the array of array problem in excel

The new Dynamic Array formulas in Excel are awesome. But it is very common to run into a known limitation. It is called an array of arrays and it is happening a lot to people who are trying to learn dynamic arrays.

Table of Contents
(0:00) Problem Statement: No array of arrays
(0:20) Do all VLOOKUP in one formula
(0:30) XLOOKUP can return all columns
(0:47) Can not lookup all rows and return all columns
(1:10) Array of Arrays is not currently supported
(1:45) Clicking Like really helps the algorithm

[excelisfun]

Thanks, Mr Excel!!!! I can't wait for arrays of arrays to be allowed : )

[AjayAnandXLnCAD]

Oh. I missed this one 😀 Clear and to the point.

[mrpennington]

Absolutely loving dynamic arrays, so I can live with some shortcomings. I discovered something I've wanted to do for a while, today and that is to use a dynamic array with a mod function to create a repeating list. So this formula =IF(MOD(SEQUENCE(100), 10)=0, 10, MOD(SEQUENCE(100), 10)) will produce 100 rows of 10 repeating arrays of 1 to 10. Something I've often wanted to do when creating waffle charts. At any rate, appreciate all of the great content, as always!

[WebberJason]

Really glad I watched this. I have just run into a similar issue trying to test whether a date (part of a spilled range) falls between two other dates, where I6# is a range of dates: =AND(I$6#>Start_Date, I$6#<=End_Date) returns a single true or false result instead of a spilled range of trues and falses. It seems this issue applies even when the two arrays reference a single array range. Here's hoping "arrays of arrays" are supported soon!

[vhc]

For this use case you use index and match but you can put multiple answers into match or xmatch ... or use Filter

[andrewmoss]

Hi Bill, another limitation I have noticed is with spilled ranges (eg. A1#). They don't seem to work with functions like SUMPRODUCT, AGGREGATE and nested SMALL + IF statements. They do, however, work with functions like SUMIFS, COUNTIFS, MAXIFS and MINIFS. I'm just wondering if this is something you had already noticed.

[GeertDelmulle]

Won’t the FILTER function do something similar to what you tried there?

[patrickschardt]

That would be awesome if we could do this

[mchllwoods]

That means u can't use # either???

[ennykraft]

Can anybody explain to me why dynamic arrays won't work in tables, even when I just try to spill one row across several colums? That really would make them even more useful and I don't understand what's the problem with that.