I've been looking for this for so long! So many videos about the new array functions but few about using the new dynamic arrays with more classic functions. Thank you!
Exactly!!! For me, Dynamic Arrays are not about the new functions, but that almost every other function has now had an upgrade. Even without the new functions, Dynamic Arrays would have been huge 😀
Really good man. I very much liked the background information about implicit intersection. I now finally understand the control shift enter thing, never really knew what that was!
Great videos. I've watched all the videos about the new array formulas and you may be the best Excel instructor around. No frills but outstanding. Very clear.
I agree. There are a few excellent instructors out there but you are very concise and offer ton of valuable information using the current content format to publish your videos. Congratulations for your excellent job! 👏🏻👏🏻👏🏻
Thank you for sharing this valuable information. I didn't know how many new tricks Excel brought along. Question, how come the sumifs formula doesn't give you the pound sign? I am mirroring your formulas in 365 and it was all similar except the pound sign.
I knew about the new XLOOKUP function, but didn't fully understand the impacts of the new dynamic arrays - the implications of this change are pretty cool. Out of curiosity, is there any way that you've found to base the range of row numbers within the brackets in your VLOOKUP to be based off an alternate lookup? I've tried a few different options... even went with an Indirect of some sort, but it doesn't want to recognized the list. I can think of quite a few uses for that.
I'm glad it give you a good introduction into the changes to Excel. In terms of your question, I think you want to look at a VLOOKUP/MATCH or INDEX/MATCH/MATCH formula combination. I have a post about INDEX/MATCH/MATCH here: exceloffthegrid.com/index-match-match-in-excel-2-dimension-lookup/ Trump Excel has a good post about VLOOKUP MATCH here: excelchamps.com/blog/vlookup-match/
@@ExcelOffTheGrid Thanks. I'm familiar with the formulas, I was just excited at being able to pull multiple data elements for multiple lookups with a single formula and was looking for a way to designate the column references all in one shot. Great video, though - I'll be checking out the rest.
Hello, How can I use # operator with Xlookup function, where the no. of rows in the data source can fluctuate from one instance to another? Can an example be provided regarding syntax?
In older Excel suppose in any single cell we write =B2:B$6 and then press Ctrl+Shift+Enter and then we drag that cell below. What happens in the sixth cell down, is {=B$6:B7} Surprising, na? Why this happens? Is there any explanation?
@@ExcelOffTheGrid I figured it out. The F2# thing does not work unless the data you want to "select" is an array. For the VLOOKUP thing going to try when I can get to my PC
I admire the clear way you break down the functions and appreciate the example files. Thank you. You are good at what you do.
Thanks Robert, that is very kind of you to say 😁
I've been looking for this for so long! So many videos about the new array functions but few about using the new dynamic arrays with more classic functions. Thank you!
Exactly!!!
For me, Dynamic Arrays are not about the new functions, but that almost every other function has now had an upgrade.
Even without the new functions, Dynamic Arrays would have been huge 😀
Really good man. I very much liked the background information about implicit intersection. I now finally understand the control shift enter thing, never really knew what that was!
Great videos. I've watched all the videos about the new array formulas and you may be the best Excel instructor around. No frills but outstanding. Very clear.
Thanks Diego. I appreciate that. Dynamic arrays are certainly great addition to Excel. 👍
I agree. There are a few excellent instructors out there but you are very concise and offer ton of valuable information using the current content format to publish your videos.
Congratulations for your excellent job!
👏🏻👏🏻👏🏻
The best intro of DA in excel.. Best tutor.. Keep up the good work..
Wow, thanks that's very kind of you to say :-)
Excellent clarification for the dynamic array, thanks!!!!!
You’re welcome. Glad I could help.
The best intro of Dynamic Array in excel
Thank you Manoel - that's very kind of you to say.
Mark Proctor!!! Where have you been all my life??? Amazing content you've got!
I've been right here waiting for you to find me .... :-)
Thanks for this video, help a lot to understand dynamic arrays
Thank you - I’m glad it helped 😀
Excellent video to understand the new features of dynamic array functions
Thank you - I’m glad you found it useful.
Thanks about the constant array bit
Thanks - I think a good understanding of constant arrays is key to understanding this topic.
Great breakdown!
Thanks. 😀
Thank you for sharing this valuable information. I didn't know how many new tricks Excel brought along. Question, how come the sumifs formula doesn't give you the pound sign? I am mirroring your formulas in 365 and it was all similar except the pound sign.
Without seeing your screen, I'm not sure. Does it work correctly when you add/remove the # sign? That's the most important question.
Great vedio we use this option now in Excel 2021 v
one word: perfect!
Even if you went with two words ‘almost perfect’, I still would have been happy with that 😀
I knew about the new XLOOKUP function, but didn't fully understand the impacts of the new dynamic arrays - the implications of this change are pretty cool. Out of curiosity, is there any way that you've found to base the range of row numbers within the brackets in your VLOOKUP to be based off an alternate lookup? I've tried a few different options... even went with an Indirect of some sort, but it doesn't want to recognized the list. I can think of quite a few uses for that.
I'm glad it give you a good introduction into the changes to Excel.
In terms of your question, I think you want to look at a VLOOKUP/MATCH or INDEX/MATCH/MATCH formula combination.
I have a post about INDEX/MATCH/MATCH here:
exceloffthegrid.com/index-match-match-in-excel-2-dimension-lookup/
Trump Excel has a good post about VLOOKUP MATCH here:
excelchamps.com/blog/vlookup-match/
@@ExcelOffTheGrid Thanks. I'm familiar with the formulas, I was just excited at being able to pull multiple data elements for multiple lookups with a single formula and was looking for a way to designate the column references all in one shot. Great video, though - I'll be checking out the rest.
A part from using a constant array, like {1,2,3}, I think another function is the only option.
great explanation
Thanks Frank. 😀
@@ExcelOffTheGrid likewise 🙏
Hello,
How can I use # operator with Xlookup function, where the no. of rows in the data source can fluctuate from one instance to another? Can an example be provided regarding syntax?
It depends on circumstance.
Are the lookup_array and return_array arguments based on individual spill ranges, or one large single spill ranges?
In older Excel suppose in any single cell we write =B2:B$6 and then press Ctrl+Shift+Enter and then we drag that cell below. What happens in the sixth cell down, is {=B$6:B7}
Surprising, na? Why this happens? Is there any explanation?
This does not work en 2021 version, right? I'm trying to do the F2# and the VLOOKUP {X,Y} examples and does not work.
You can do F2# or {X,Y} in a VLOOKUP, but not both at the same time.
@@ExcelOffTheGrid I figured it out. The F2# thing does not work unless the data you want to "select" is an array. For the VLOOKUP thing going to try when I can get to my PC
Yep, it's the same, you need an array to use the function. So useful! Thanks for the vid!!!
@@Sourenics Yes, the # refers to the spill range.
@@ExcelOffTheGrid The vlookup shows the data in vertical, can't make it to show the values in horizontal (spanish excel).
when I put # behind a cell, It not work and return #REF error. Is there any solution. I use 365
Go back to the video 14:10 where Mark talks about referencing methodology, depending on your country you may have to use “pound” not “hash”; good luck
@@sebfromgermany3819 thank you
Thank you too much
You are most welcome