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.
Transcript of the video:
Learn Excel from MrExcel Episode 2314. No Deal on Array of Arrays.
Hey, welcome back to MrExcel Netcast. I'm Bill Jelen.
A lot more people are getting dynamic arrays right now because it's rolled out to the monthly channel.
And this question keeps coming up in the boards and I just thought I should cover exactly what's going on here right?
First thing, thanks to dynamic arrays, we can now do crazy things like doing all of these VLOOKUPs at once.
So a single formula returns all of the answers for Q1 that's beautiful.
Also XLOOKUP as I think I've covered before has the ability to return an entire set of columns at once.
That's beautiful! But while we can go and do all of those lookups at once or return all of those answers at once, the thing that we're not allowed to do is to say that we want to look up all of these values.
And oh, by the way, when you find it, return all of those answers, it seems like a natural thing. I mean, it can go vertical.
It can go horizontal.
Why shouldn't it be able to go vertical and horizontal at once?
But it won't, and people are posting on the boards. Hey, why won't this work?
And the deal is it won't work.
Joe McDaid calls this an array of arrays.
And, currently, an array of arrays is not supported.
He's not against it, he just hasn't gotten gotten the time to fix it yet. Alright, so for right now, here we are.
February 2020. Not allowed to do an array of arrays.
You know, in all week these podcasts I've been doing where I throw dynamic arrays in there is like, Oh, I can make this really good if I would just extend it in another direction.
But now it doesn't work. So I love the dynamic arrays.
Just be aware there's times where you can't do a two-way kind of an array.
All right, hey my book, Excel Dynamic Arrays Straight to the Point - click that "I" in the top right-hand corner and please Subscribe and Ring the Bell.
Feel free to post any questions or comments down in the YouTube comments below.
Thanks for stopping by.
We'll see you next time for another netcast from MrExcel.
Hey, welcome back to MrExcel Netcast. I'm Bill Jelen.
A lot more people are getting dynamic arrays right now because it's rolled out to the monthly channel.
And this question keeps coming up in the boards and I just thought I should cover exactly what's going on here right?
First thing, thanks to dynamic arrays, we can now do crazy things like doing all of these VLOOKUPs at once.
So a single formula returns all of the answers for Q1 that's beautiful.
Also XLOOKUP as I think I've covered before has the ability to return an entire set of columns at once.
That's beautiful! But while we can go and do all of those lookups at once or return all of those answers at once, the thing that we're not allowed to do is to say that we want to look up all of these values.
And oh, by the way, when you find it, return all of those answers, it seems like a natural thing. I mean, it can go vertical.
It can go horizontal.
Why shouldn't it be able to go vertical and horizontal at once?
But it won't, and people are posting on the boards. Hey, why won't this work?
And the deal is it won't work.
Joe McDaid calls this an array of arrays.
And, currently, an array of arrays is not supported.
He's not against it, he just hasn't gotten gotten the time to fix it yet. Alright, so for right now, here we are.
February 2020. Not allowed to do an array of arrays.
You know, in all week these podcasts I've been doing where I throw dynamic arrays in there is like, Oh, I can make this really good if I would just extend it in another direction.
But now it doesn't work. So I love the dynamic arrays.
Just be aware there's times where you can't do a two-way kind of an array.
All right, hey my book, Excel Dynamic Arrays Straight to the Point - click that "I" in the top right-hand corner and please Subscribe and Ring the Bell.
Feel free to post any questions or comments down in the YouTube comments below.
Thanks for stopping by.
We'll see you next time for another netcast from MrExcel.
