How to Program Excel’s Dynamic Array Functions

How to Program Excel Dynamic Array Functions in Microsoft Excel in 2024 is far different than it was just a few years ago.  The Microsoft Corporation has been making a lot of significant changes to Microsoft Excel.  First, and most importantly, Microsoft rebuilt Excel’s Calculation Engine in 2018.  Since then Microsoft released a series of new Dynamic Array Functions for Office 365, starting in 2020.


– You can make almost any function a dynamic function, a function that Spills –


In this post:  Here we teach you exactly how to program Excel 365 using not just the new Dynamic Array Functions, but all functions.  (Yes, most functions can be made dynamic, I.E., to Spill).

New Function Example:  The most used of the new Excel 365 functions is the XLOOKUP—possibly the most powerful function to come to mind, if you exclude the LAMBDA Function.   The XLookup replaces the VLookup, HLookup, Lookup, Index/Match, etc., and it Spills, either vertically or horizontally, but not both, at the same time.

Excel 365 is in a Constant State of Flux:  Changes are still coming out today, and there is no end in sight.  It is time to learn how to program the new Dynamic Excel.  And it is time to learn how to make the traditional functions dynamic as well.  Here we will show you much of what you need to know.


The most important change for professional Excel programmers is the new Dynamic Array Functions, and their ability to Spill.  These functions greatly simplify Excel Programming efforts.  It is time to take a second look at how you program Microsoft Excel.


Learn to Master the use of Referencing the Spill Range.

Top Secret; you don’t know this:  Many people do not know this, but you can make most of the traditional Excel functions, say the VLookup, dynamic.  You can force a VLookup to Spill, just as you can with the traditional SumIfs Function.

That is what we teach you in this post, how to make legacy Excel functions Spill.

The ability to create a Spill Range changes the game You want to force the Spill Range; you need to find ways to create it. Then you can reference it.




How to Program Excel Dynamic Array Functions Blog Post: Animated Gif from Microsoft on Referencing a Spill Range

In 2024 the top Excel experts focus on building models that produce a Spill Range. Then we can use the # to make all of our functions dynamic. Excel programming is now Range-Based, not Cell-Based. Learn to use the #.



An Excel function in one cell can now Sort, Filter, expand into thousands of cells, simply based on the data.



In this post we cover these Excel Functions:

Here we make older Excel functions Dynamic as well.

All of these will produce a Spill Range.  Even the older functions ( Legacy Functions) below can produce a Spill Range.

Here we will show several examples of some of the most used functions in Excel, and most importantly, how to use them in their Dynamic state, to produce a Spill Range.

Here we will Spill the following functions, in detail, showing you how to make them Dynamic and thus Spill:

  • VLookupLegacy Function
  • Xlookup
  • XMatch
  • SumIfLegacy Function
  • Combined with these functions:
    • Sequence
    • ChooseCols
    • Unique
    • Filter
    • Sort



If you need help programming your Excel files, we have a team of expert Excel consultants to assist you.




How to make the Legacy Excel Function Produce a Spill Range

The VLookup still has its place, there are plenty of people not using Excel 365, and they do not have access to the XLookup, so knowing how to use the VLookup still matters.  And now that it can be made dynamic, it is more powerful than ever.  But you should use the XLookup whenever possible.

Let’s use the Most Used Excel Function of all time as our first example – VLookup


How to Program Excel Dynamic Array Functions Blog Post: Image of the Functions Argument window for the VLookup Function

The XLookup has largely replaced the VLookup as the most used function in Excel. But do not throw the VLookup out, it still has its use, and get this, the VLookup can be just as dynamic as the XLookup. If you know what you are doing.



Programming the Microsoft Excel VLookup Function Demonstration

The Microsoft Excel VLookup is the most searched, and most used Excel function of all time.  This function is the backbone of many Excel workbooks.  But the VLookup has its flaws, hence the use of Index/Match.  The VLookup only looks to the right.  Still, this function is widely used, and should be understood.

The VLookup is not one of the new Dynamic Array Functions.  Yet, if you know how to use it, and if you are in Excel 365, the VLookup too returns a Spill Range.  Here we teach you How to Program Excel Dynamic Array Functions.


The VLookup prior to 2018 cannot Spill

This is what a traditional VLOOOKUP Function looks like:  =VLOOKUP(M6,D6:J22,7,FALSE).

Pretty straightforward, but 1) you do not know what the lookup is looking at, no use of Named Ranges, and you must put the function in every cell in the Range.

Same VLookup, but using Named Ranges to add meaning to the results the function is providing:  =VLOOKUP(M6,tblVLookupExample,ColumnToReturn,FALSE)

Often, you need to add Iferror to the VLookup to account for errors, the XLookup nicely has that build in.


How to Program Excel Dynamic Array Functions Blog Post: Animated Gif from Microsoft website on Spill Range being blocked.

When you see a Spill Error, “#SPILL!” that means there is not enough room for the range to spill, hence the spill error.


The VLookup post 2018 can Spill

The changes to the Excel’s calculation engine that took place in 2018 breathed new life into the VLookup, giving it added power and ease of use.  While you should always try to use the XLookup, being able to make the VLookup dynamic is an important skill to have.  Each tool has its uses.


The ability to Spill makes all the difference; this is Range-Based Programming.   Learn How to Program Excel Dynamic Array Functions.


Three ways to create a Spill Range in Excel 365 

  1. Use one of the new Dynamic Array Functions, such as Filter, ChooseCols, GroupBy, etc.
    1. They automatically create a Spill Range, Spill Cell.
  2. Select multiple cells for arguments, not just one cell.
    1. Select a Range as the “Lookup_Value” for example, in the VLookup.
      1. This will result in a Spill Range
    2. Select a Range as the “Col_Index_Num”,  perhaps using Sequence or ChooseCols.
  3. Add the # to your Excel functions, if they are referencing a Spill Range, they will Spill as needed.
    1. This is the way to go, also great for drop-down Validation Controls.  So simple, so powerful.


Learn all three ways to create a Spill Range in Excel


Expert’s Note:  This means you can make the older Excel functions dynamic as well.  That is huge.  Excel really has never been easier to program.   Dynamic in Excel versions 2019 on; you need the new calculation engine to for Excel to be dynamic.


It has never be easier.  Now your results will grow and shrink with the data range being referenced.  No need to copy down the formula, those days are over.  Per row or column, just one formula, in one cell.

Add #:  Add the # to make the VLookup Dynamic:


Criteria Range:   You can select multiple criteria for the VLookup, enter it in one cell, it will Spill into the Range, and you do not need to use the #.

=VLOOKUP(M6:M32,tblVLookupExample,ColumnToReturn,FALSE) as in the image below.

You are learning advanced skills.


Multiple Criteria simplifies your programming.

In the image below, only cell AB6, the Blue cell with the 833 in it, has a formula, the rest are in memory, this is a Spill Range based on a Dynamic VLookup. It is dynamic because it is looking at a range and not a cell.  Notice cells C6:C21, multiple criteria cells, not one; will result in a Spill Range.    So simple to learn how to program Excel Dynamic Array Functions.


How to Program Excel Dynamic Array Functions Blog Post: Image of Excel, and of the VLookup function being used.

The Microsoft Excel VLookup is able to produce a Spill Range, just like the new Dynamic Array Functions. Select multiple criteria cells, as we did here. The first cell has the calculation, the rest is the Spill Range.,

C6:C21 Did you see that?     It works!


Advanced VLookup Function – Select Multiple Columns

Here for the VLookup, we used Sequence to designate the columns to return.  Not, not the column to return, rather which columns, and as such, one VLookup can populate the entire row.


This single VLookup returns 5 columns of data, no need for the #, nor to reference a Spill Range, it creates one.


How to Program Excel Dynamic Array Functions Blog Post: Same as image above, but different function.






More Looks at Programming the Excel VLookup post 2018

 – You can apply these concepts to most Excel functions, new and old – 


What we show you on the VLookup can be used with most Excel legacy functions.  The SumIfs, CountA, Sum, Concatenate, all can be made dynamic.  Either select multiple criteria, or use the #, when referencing a Spill Range, and you will have a dynamic function. ( Some functions will not allow you to do this ).


Important Skills:  One of the most important skills you can have with Excel functions, is knowing how to combine them, say 2-7 functions per formula.    This is where the real power comes in, and with these new functions, the results that you get are more database type results.  One cell, one complex formula, composed of several Spillable functions.  This is how the experts program Microsoft Excel. 




How to Program Excel Dynamic Array Functions Blog Post: Image of the Excel VLookup being used to produce a Spill Range.

Notice the first parameter of the VLookup Function in the image above is looking at a Range, not a cell. This makes the function dynamic; point it at a Range, when that Range grows, the formula results expand.



Ways to Create a Dynamic VLookup Function:

Look at these examples to Learn How to Program Excel Dynamic Array Functions:

  • =VLOOKUP(tblVLookupExample[Staff],tblVLookupExample,3,FALSE)
  • =VLOOKUP(M6#,tblVLookupExample,ColumnToReturn,FALSE)
  • =TRANSPOSE(VLOOKUP(NamedSpillCell,tblVLookupExample,SEQUENCE(5,1,3,1),FALSE))
  • =TRANSPOSE(VLOOKUP($U6,NamedRangeExample,VLookupSequenceSpillCell#,FALSE))
  • =TRANSPOSE(VLOOKUP($U6,NamedRangeExample,SEQUENCE(5,1,3,1),FALSE))
  • =VLOOKUP($BA6#,NamedRangeExample,BB$23,FALSE)
  • =VLOOKUP(D6:D21,tblVLookupExample,3,FALSE)
  • =VLOOKUP(“Tom”,VLookupRangeExample,{1,2,3,4},0)
  • =VLOOKUP(“Tom”,VLookupRangeExample,InputNumbers,0)



Expert’s Note:  Vertical versus Horizontal Spill:    With the dynamic VLookup, you have to choose to Spill either Vertically or Horizontally, you cannot do both.  In most cases, I choose to Spill vertically, so there is no need to copy the formulas down.  Thus, each column would have its own formula, also forcing a Spill Range.




Very Important Note:  You still cannot use DAFs in Excel Tables, unless it does not Spill.  You can use the XLookup in a Table, but the results must populate just one cell in the Table.            You cannot Spill in an Excel Table.




Let’s Look at the Excel XLookup Function

The XLookup is quickly becoming the most used Excel Function, replacing the long standing VLookup.  So basically, a Lookup Function is still the most used function in Excel 365.   Let’s see the various ways we can use the Microsoft Excel XLOOKUP Function.


Basic XLookup:   =XLOOKUP(N6,$E$6:$E$21,$G$6:$G$21)

Advanced XLookup, “lookup_value” is looking at a Range:  =XLOOKUP(S6:S21,$E$6:$E$21,$G$6:$G$21),

XLookup, w/ #:     =XLOOKUP(Y6#,$E$6:$E$21,$G$6:$G$21)

Bob’s XLookup, w/ #:    =XLOOKUP(InputCell,LookupRange,ResultRange)#

Dual XLookups:  =XLOOKUP(K7,E7:H7,XLOOKUP(K9,D8:D10,E8:H10,”-“),”-“)

Two XLookups, Two Tables:




Using Multiple Lookup Values Option in XLookup to Spill:


Here, in the “Advanced XLookup” example above, the “Lookup_Value” argument is showing a range (S6:S21), rather than just S6.  That is important to note.  And you can do that with many of the legacy functions, such as the VLookup.

I repeat, you can select multiple cells to be evaluated and thus returned.  This is very useful.  If you do it this way, you will not need to use the # nor reference a Spill Cell.   Doing this will actually create a Spill Range (aka, Spill Cell).


Expert’s Advice:  Remember this: Select Multiple Criteria Cells as the Input to the XLookup Function


XLookup Example 1: Multiple Lookup Values, Spills Vertically.

How to Program Excel Dynamic Array Functions Blog Post: Image of XLookup being used.

Excel 365 allows you to select multiple cells for the “Lookup_Value” argument. Thus you do not need to use the #, to Spill your data.



XLookup Example 1:  Spills Vertically

How to Program Excel Dynamic Array Functions Blog Post: Second image of XLookup for example.

Cell T6 has the function in it, the cells below are being Spilled. Thus, the function is only in one cell, and it will grow and shrink along with the data. This is powerful Excel programming.



XLookup Example 2: Multiple Columns Selected for return Array.  Spills Horizontally.

How to Program Excel Dynamic Array Functions Blog Post: Image of XLookup, Spilling Range.

The XLookup Function can Spill either vertically or horizontally, but not both, at the same time.




Microsoft Excel XLookup Using The #

While you can select multiple Lookup Values, a Range of Lookup Values, it is often easier just to point to the first cell of a Spill Range, and then add the #.  Thus making the function dynamic.  You should use this method as much as possible.  This is how Microsoft Excel works now.



How to Program Excel Dynamic Array Functions Blog Post: Image of XLookup using # to make it dynamic.

Referencing a Spill Cell, or Spill Range using the # is the easiest way to make any Excel function dynamic, aka, Spill. You should use this method as much as possible, when programming Microsoft Excel.



Bob’s XLookup is Ultra-Advanced

Look at the example below.  The Blue shaded cells are each the first cell of a horizontal Spill Range.  Each Blue Cell has a function such as “=SEQUENCE(,4)”.  Sequence is a Dynamic Array Function, and thus Spills, here horizontally.  So each row is a Spill Range.

The Blue shaded section is named “ResultRange”.   So we are pointing to the first cell in the Spill Range, what I call the Spill Cell.  We can now reference this range, with the #, dynamically.  Each cell in the Range is a Spill Cell, that is what makes this possible; we are using an array function to return an array.

By adding the # at the end of the XLookup Function, we will return the Array in that cell/row, versus just that cell.  If you remove the # you will get just the first value in that row.  Hence the power of the # in Excel 365.

A little different, I know.  I first saw Leila do this, and then Bob created an example similar to what is below.   I have not seen anyone else do this.  To learn more, check out Leila’s videos on LinkedIn Learning.


How to Program Excel Dynamic Array Functions Blog Post: Image of workbook, showing Bob's XLookup.

The # at the end of the XLookup Function. That is interesting. Makes you think.



Other Uses for the XLookup


XLookup as Offset Function Example

Of course you would never do what the XLookup below is doing, but it is important to note, the XLookup uses two arrays (Lookup Array, Return Array), and they do not need to be in the same Range, Table, Tab, or Workbook.

* And to make it even more interesting, you can select multiple cells for the Lookup Value, also making it Spill, but you are limited to a single Spill, vertical or horizontal, not both.

Note:  We actually did do this in a file where we used the XLookup to bring in sloppy PDF data.   The PDF brought in the data on different rows, consistently in different rows, so we used the XLookup to extract the data that we wanted.  What we needed was the ability to Offset, and the XLookup has that feature built in, so we used it.

But Note: the function below will not Spill multiple Return Array Columns, just the first, due to the multiple cells selected in the Lookup Value Argument.



(The image below is not from that file, but rather strictly created for this demonstration.)

Image of Excel Table, being referenced by XLookup.

The XLookup is one of the most diverse functions in Excel. It even Offsets.




Dual XLookup XLookups Combination

Returns a single value, same as Index Match Match.

Do you want to return a single value from a range, based on two sets of criteria (One horizontal, the other vertical)?  If so, you can easily use the Microsoft Excel XLOOKUP function, twice.

You can do this with Index XMatch XMatchYou can achieve the same results with a series of functions.


How to Program Excel Dynamic Array Functions Blog Post: Image of two XLookup functions. This is the advanced use.

The XLookup when used with a second XLookup functions much like Index/XMatch/XMatch, returning a single value at the intersection of two.



Two XLookups Xlookups in One

Second XLookup is in “If_Not_Found” Argument

If it is not found in the first Table, then look in the second Table. You can repeat this, as many times as needed.  You can in fact place almost any Excel function in that argument.

Think outside the box; how far ban you bend Excel without breaking it?





Index XMatch XMatch Combination

Another way to get the same end result as XLookup XLookup above.  In this example the user selects the Quarter and the Staff, using the two drop-down validation controls.  The result is the intersection of the criteria.  A single cell, not a spill range.


Image of Index XMatch XMatch. The combination of functions is what you want to watch.

If you need to find a value based on two criteria, one vertical, the other horizontal, then, this combination of Excel functions might be right for you.





Microsoft Excel SumIf Examples



SumIf Function is Dynamic in Excel 365

Not a Dynamic Array Function. This result populates a single cell, not  range.  This is how you are used to seeing the SumIf and SumIfs functions, populate one cell, one formula per cell.

The SumIf is the second most used Excel function, right after the XLookup.  The ability of each of these to return a Spill Range just makes them that much more powerful.  Sumifs, Excel Tables, and XLookups, other than Power Query, what more do you need.


Image of Excel Table and the SumIfs Function.

Here is the pre-Excel 2018 SumIfs Function, returning a single value, based on two criteria.



Dynamic Excel SumIf Function with the #

Add the # to the SumIf in the image below, and you have a Dynamic Excel SumIf function.   In the image below, cell P6 has the DAF, and you can see that it Spills the results.  No need to copy the formula down as the data range grows.  The simplicity of the dynamic arrays is what makes them so easy to use, yet so powerful.


Image of SumIf being used to produce a Spill Range.

Here is the post-Excel 2018 SumIf Function, which is dynamic. Here we use the # to Spill the results.



Dynamic Excel SumIf Function without the #

Here we choose to select the entire data range in Column N, instead of just one cell in the range.  The result, this produces a Spill Range, just as the # did, but without the #.

Two ways to do the same thing, based on your needs.

See, you can make almost any Excel function dynamic, if you are using Excel 365.


Image of SumIf using a range of inputs to make it dynamic.

Why do you want your Excel functions to Spill? So that the formula will grow and shrink, as needed, with the data. It simplifies the use of Excel.


You can make most Excel functions Dynamic

We have shown how to take the two most used Excel Functions (VLookup, SumIf), that are not one of the new dynamic array functions, and we have shown you how to make them dynamic.  Here we have shown you how to make older functions produce a Spill Range.  Try it with AverageIf, Index, Match, etc.

The thing you will to learn is when to apply each method?  As with all things Excel, there are many ways to do the same thing.  As such, the better you know the tools, the more you can do in Excel.  Use the best tool for the job, be it the VLookup or the XLookup.  And then learn to make them dynamic.




When to use each Dynamic Method to Force a Spill Range?

The new Excel Dynamic Array Functions will produce a Spill Range, they are in design Dynamic Functions.   But what about the older functions, such as AverageIf, which is not a DAF, how do you make those Dynamic, IE. Spill?

You can make most Excel functions dynamic by using either of these two methods.  Which method to use depends on the data you are referencing, does the data range grow and shrink, or does it remain constant.

When to use the # versus selecting multiple inputs, is easy:

  1. If the range might grow or shrink, use the #.
  2. If the range will stay the same size, then select multiple criteria.


Expert Note:  One method requires you to reference a Spill Range, the other does not.  But both approaches will produce a Spill Range, just like the new dynamic array functions.


How to Program Excel Dynamic Array Functions Blog Post: Image of the DAFs in Excel.

Here the list of names will not grow; the size of the data range will not change. So we choose to select all of the Staff in the Lookup_Value argument of the XLookup function. We selected the entire list of names, thus making the function produce a Spill Range.





Combining two or more Dynamic Array Functions into one Formula

This is where the real power is, combining several dynamic array functions into one.  Possibly adding in several legacy functions as well, it works.

This is range-based Excel programming.  You write one complex function, in one cell, and it populates a Spill Range.  This is how you program in Excel 365.

Some of the functions help to put the data-set in a certain order, filtered, etc.

The function below creates the output in the image below.  This is an interactive dynamic array function dashboard.  One cell, one complex function.



Image of a report based on the dynamic array functions.

The Dynamic Array Functions are designed to produce a range of output, like a database. One cell, one function, that Spills. This is the future of Excel programming.





Here the user can pick the columns that want to include in their report, and they are able to set the order of the columns.  They can even filter the results, all based on two combined Excel functions.  This is the power of dynamic array functions.  A game changer.


Image showing how the Filter Function can be used.

Here is an example of using the Filter Function to determine what data is returned.


Pointing Dynamic Array Functions at Excel Tables

Do you need to create a unique list of data, sorted, and possibly filtered, from an Excel Table?  It could not be easier.  A great way to populate a drop-down Validation Control, when you want to control what is listed.


The combination of Unique, Filter and Sort is possibly the most used combination of these new powerful functions.


Image of Excel's Utility Functions being used.

Excel 365 Utility Functions = Sort, SortBy, Filter, Unique, Sequence, Drop, Take, Transpose,




The Filter Function is amazingly powerful, one of the most used “Utility” functions in Microsoft Excel 365.

Here we use the dynamic array filter function to produce a report, from an Excel Table.  The newer functions are the future of Excel programming, in the combination of them.


Image of Excel Table with Slicers and a DAF Report.



Excel Should be Easy to Use

With these new functions you can easily create interactive reports.  Placing the users options outside the formula means you can use dropdown lists to determine what data is shown, and even how it is show.  Without the use of VBA.

Excel is now easy to use, and it is also easy to program Excel Dashboards and Reports.


Image showing how to use ChooseCols Function.

The ChooseCols Function is my favorite function in Excel 365. Combine it with other functions to create interactive reports and analysis.


The Future is Here:  One Complex Function, in one cell, Populates Entire Range


Learning how to write the XLookup Function is one thing, being able to develop an entire file based on Excel Tables, Power Query, Dynamic Array Functions, and Pivots is another.  The real power is in knowing how to combine several functions into one.  One that completely provides the output you need, based in one cell, Spilling to others.

If you want to be an Excel expert, this is what you need to master.


This simple example shows you the future of Excel programming. One cell, one complex function.
I termed this range-based programming.





We can help your Business with Microsoft Excel’s Dynamic Array Functions


As you have read, Microsoft Excel is in a continuous state of change.  It is a good thing, greatly appreciated.   Excel 365 is not the same as Excel was in 2016.  As such, it is time to learn how to Program Excel’s Dynamic Array Functions.

Depending on your skills, there are two ways we can help with programming Microsoft Excel’s new Dynamic Array Functions.

  1. While it might not be hard, perhaps you do not have the time.  In that case we recommend that you take advantage of our one-on-one Excel mentoring services.
  2. If you need a custom-built solution, we are here to help.  We offer free consultations, we do amazing work, and our rates are affordable.


It starts with a conversation.  It ends with your organization having the custom solution you so desperately need.



Toll-Free: 877.392.3539Irvine, California: 949.612.3366Manhattan, New York: 646.205.3261

If you need help with Programming Excel Dynamic Array Functions – Give us a call

If you want to learn How to Program Excel Dynamic Array Functions, give us a call.  we offer mentoring and programming services.

Contact us for a Free Consultation Today. Operators are standing by to take your call.   Get a Smarter Solution.


Image of our company logo.