Dynamic arrays in Excel | Understanding the biggest change to Excel.... ever | Excel Off The Grid

Поделиться
HTML-код
  • Опубликовано: 13 дек 2024

Комментарии • 54

  • @seanwang2514
    @seanwang2514 7 дней назад +1

    Great video! The way you articulate this is so easy to understand. Can't believe it took me 4 years to find this gem.

  • @roberth.9558
    @roberth.9558 Год назад +4

    I admire the clear way you break down the functions and appreciate the example files. Thank you. You are good at what you do.

    • @ExcelOffTheGrid
      @ExcelOffTheGrid  Год назад

      Thanks Robert, that is very kind of you to say 😁

  • @manoellopes8071
    @manoellopes8071 3 года назад +1

    The best intro of Dynamic Array in excel

    • @ExcelOffTheGrid
      @ExcelOffTheGrid  3 года назад

      Thank you Manoel - that's very kind of you to say.

  • @diegocisneros-sf
    @diegocisneros-sf 3 года назад +3

    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.

    • @ExcelOffTheGrid
      @ExcelOffTheGrid  3 года назад

      Thanks Diego. I appreciate that. Dynamic arrays are certainly great addition to Excel. 👍

    • @ElTigreFIME
      @ElTigreFIME 3 года назад

      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!
      👏🏻👏🏻👏🏻

  • @julienbouillot6960
    @julienbouillot6960 2 года назад +3

    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!

    • @ExcelOffTheGrid
      @ExcelOffTheGrid  2 года назад

      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 😀

  • @fjbekkering
    @fjbekkering 6 месяцев назад

    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!

  • @esthersuh3388
    @esthersuh3388 2 года назад +1

    Excellent clarification for the dynamic array, thanks!!!!!

  • @Sri-Nivas
    @Sri-Nivas 4 года назад +2

    The best intro of DA in excel.. Best tutor.. Keep up the good work..

    • @ExcelOffTheGrid
      @ExcelOffTheGrid  4 года назад +2

      Wow, thanks that's very kind of you to say :-)

  • @Chukwuzitremonyeka1735
    @Chukwuzitremonyeka1735 3 года назад

    Mark Proctor!!! Where have you been all my life??? Amazing content you've got!

    • @ExcelOffTheGrid
      @ExcelOffTheGrid  3 года назад +1

      I've been right here waiting for you to find me .... :-)

  • @dileepkumaryadav1666
    @dileepkumaryadav1666 2 года назад +1

    Thanks for this video, help a lot to understand dynamic arrays

  • @cokeheadoffice
    @cokeheadoffice 3 года назад +1

    Excellent video to understand the new features of dynamic array functions

  • @stephanweaver1960
    @stephanweaver1960 2 года назад +1

    Thanks about the constant array bit

    • @ExcelOffTheGrid
      @ExcelOffTheGrid  2 года назад

      Thanks - I think a good understanding of constant arrays is key to understanding this topic.

  • @controlsgirl
    @controlsgirl 2 года назад

    Great breakdown!

  • @brunobaetenscvoscala8818
    @brunobaetenscvoscala8818 Год назад

    one word: perfect!

    • @ExcelOffTheGrid
      @ExcelOffTheGrid  Год назад

      Even if you went with two words ‘almost perfect’, I still would have been happy with that 😀

  • @MohamedAhmed-training
    @MohamedAhmed-training 2 года назад

    Great vedio we use this option now in Excel 2021 v

  • @ernstgenzs7456
    @ernstgenzs7456 3 года назад +1

    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.

    • @ExcelOffTheGrid
      @ExcelOffTheGrid  3 года назад

      Without seeing your screen, I'm not sure. Does it work correctly when you add/remove the # sign? That's the most important question.

  • @FRANKWHITE1996
    @FRANKWHITE1996 2 года назад +1

    great explanation

  • @sriamudhan2062
    @sriamudhan2062 2 года назад +1

    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?

    • @ExcelOffTheGrid
      @ExcelOffTheGrid  2 года назад

      It depends on circumstance.
      Are the lookup_array and return_array arguments based on individual spill ranges, or one large single spill ranges?

  • @jeny1279
    @jeny1279 4 года назад +1

    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.

    • @ExcelOffTheGrid
      @ExcelOffTheGrid  4 года назад

      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/

    • @jeny1279
      @jeny1279 4 года назад

      @@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.

    • @ExcelOffTheGrid
      @ExcelOffTheGrid  4 года назад

      A part from using a constant array, like {1,2,3}, I think another function is the only option.

  • @stanTrX
    @stanTrX 13 дней назад

    Thank you. Can i autofill a table using dynamic array formula, so that table will autoexpand as the dynamic array expands. P.s. i got how to handle spill error using index trick within a table, from one of your other video. I use index and unique function. But i want my table to grow as new data comes in. But it doesnt.. i have to manually enlarge my table

  • @Sourenics
    @Sourenics 2 года назад +1

    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.

    • @ExcelOffTheGrid
      @ExcelOffTheGrid  2 года назад

      You can do F2# or {X,Y} in a VLOOKUP, but not both at the same time.

    • @Sourenics
      @Sourenics 2 года назад +1

      @@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

    • @Sourenics
      @Sourenics 2 года назад

      Yep, it's the same, you need an array to use the function. So useful! Thanks for the vid!!!

    • @ExcelOffTheGrid
      @ExcelOffTheGrid  2 года назад

      @@Sourenics Yes, the # refers to the spill range.

    • @Sourenics
      @Sourenics 2 года назад

      @@ExcelOffTheGrid The vlookup shows the data in vertical, can't make it to show the values in horizontal (spanish excel).

  • @sandip_bettereveryday
    @sandip_bettereveryday 4 года назад

    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?

  • @NhiNgo-up8js
    @NhiNgo-up8js 5 месяцев назад

    when I put # behind a cell, It not work and return #REF error. Is there any solution. I use 365

    • @sebfromgermany3819
      @sebfromgermany3819 5 месяцев назад +1

      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

    • @NhiNgo-up8js
      @NhiNgo-up8js 5 месяцев назад

      @@sebfromgermany3819 thank you

  • @abdidekmuhumed4510
    @abdidekmuhumed4510 3 года назад

    Thank you too much