Our Favourite formula is INDEX. Here’s How Microsoft Changed It In 2020 With Dynamic Arrays

This post is also available in: Italian

There are over 450 different formulas in Microsoft Excel. And our all-time favourite is INDEX.

In January 2020 and Excel 365, Microsoft made a significant change to the way many formulas behave, including INDEX. For people using our Fast Excel Development Method this is an important update that may have substantial effect on the way that your workbooks perform.

The main take-away from this: If you are on Excel 365 use @INDEX in place of INDEX. Do this and you will be fine – Your Fast Excel Workbooks will run fast and clean.

Want to know why? Seen “#SPILL!” in a cell and wondered what it means? Then read on because this is a story of INDEX and dynamic arrays.

What is INDEX and why is it so great?

INDEX returns the value of a cell that sits in a range, given the row and/or column number. If you feed the formula a range and a row (and column) number then the formula will give you the value that sits in the cell.

Here is the definition of the formula. INDEX(array, row_num, [column_num])

In this context, “array” is a range of cells. row_num is a number that refers to the row number of that range. Column_num is the column number, not required if you have a range that is one column wide.

For example, here is a list of fruit in the range of cells B2:B11. The formula =INDEX(B2:B11,D3) returns the item in that cell defined by the row number given in D3. When the value in D3 is 6, the formula returns “Watermelons”.

In our Fast Excel Development Method, we use INDEX extensively because it is a fast and reliable way to join two tables of data.

This simple example is on one sheet. However, the power of INDEX is that it creates a way to pull data from different tables across 1000’s of records and quickly build out a powerful system.

Let’s say you use VLOOKUP formula a lot, and then one day, you learn INDEX (together with MATCH). You will never use VLOOKUP again. Here is an article that I wrote about INDEX as the most useful function you will ever learn.

Here is another example using it to join the Item Master and Sales Orders to bring the Item Description alongside the sales order record.

What has changed in Excel 365 in 2020 and what is a Dynamic Array?

In January 2020, Microsoft introduced Dynamic Array formulas. Array formulas return an array of values rather than a single reference. As such, they are not new, but you had to do a ctrl-shift-Enter like some kind of ninja trick and they appeared with {curly brackets} around them.

Now, Array formulas have become more prominent and are easier to perform. And with Dynamic Array Formulas, you can return a range by just typing in one cell. Like this new formula: SORT.

=SORT(B2:B11) in cell E2 creates a sorted list starting in E2. You get a range for the price of a cell!

There is a good, in-depth review of the new formula here at ExcelJet.com.

The new formulas are not backwards compatible with older versions of Excel. Even if you create them in Excel 365, they will not work in Excel 2016 and before.

For this reason, we won’t be using them in the standard downloads and tools that we provide, or in the Fast Excel Development Template that we offer.

Some of these new formulas are pretty cool, but you won’t need to learn them to keep up-to-date with our methods.

The issue is that INDEX has changed to become a Dynamic Array too, if used with a zero in place of the row number.

If used with a zero, INDEX will return a whole array of cells directly below. So, =INDEX(B2:B11,0) returns an array that lists all the values in B2:B11.

If those cells are blocked, then you get a SPILL! error. See the animation for a comparison.

This is an issue with the Fast Excel method because in large volumes, array formulas will work much more slowly than normal reference formulas.

If there is ambiguity about whether it is an array or a reference, then Excel will go into some kind of slow-mo brain tangle.

The result will be unnecessarily slow workbook. And if you are calculating with 10,000’s or 100,000’s rows then INDEX formulas that refer to a zero means the whole routine can take 30 minutes when normally they would do it in 8 seconds!

Here is a real example.

This is an Advanced Capacity Planning and Scheduling tool that we were building as part of our course: How to Build Planning Tools in Excel.

We built a Capacity Planning tool that exploded 5500 BOM records and calculated the capacity required for every manufactured item.

In the ScheduleCalcs sheet, there was a reference to the Routing. And the Routing Row appeared in a Pivot Table, as you can see in this screen shot.

In the pivot template, a reference to a pivot table will return an empty cell in row 8.

An empty cell returns zero in a cell reference. And we use row 8 to set the formula.

Excel 365 will resolve this as an array formula. Row 10 has the table headings, so this will block the array, giving a #SPILL! error. When it is pasted down it is blocked in every cell and has to resolve all those errors. And even more critically, other sheets that have INDIRECT formula will be very much slowed down.

I realise this is quite deep into Excel technique, so I won’t go further and explain why this happens.

It is enough to know that if you have Excel 365 and you use INDEX then you might have a problem with slow workbooks. And if you use INDIRECT and other volatile functions then this problem will be made much, much worse.

Now, I want to give you the solution. And fortunately it is very simple. As simple as adding a single character.

How to resolve the INDEX problem

To avoid this problem, you just need to add “@” before the INDEX formula.

Because “@” or the “AT-sign” is a “Implicit Intersection Operator“. See? That clears it up then.

Oh. That’s not any clearer? No, neither was it for me until I did more reading.

Apparently “@” or the “AT-Sign” forces Excel to read the INDEX formula as one that is meant to return a reference. so it no longer gets confused by the array.

It makes INDEX behave like it always did. Before Microsoft made this change.

The thing is, if you use a lot of INDEX formulas then you can never be sure how the reference in row 8 is going to resolve. At least, not without a lot of laborious checking. So, it is easier to get into the habit of turning every INDEX into a @INDEX.

Just be careful using FIND-REPLACE for that. Because you might end up with a lot of @INDEXINDEX junk in your formulas! And doing a FIND-REPLACE may still have compatibility issues when you then give your workbook to users with older versions of Excel.

If you use the “@” AT-Sign (as a implicit intersection operator, like @INDEX(… ) when you enter the formula the first time around, then your @INDEX formulas will work fast and reliable. With no penalty or negative side-effect.

Excel versions and a Compatibility Check

It is worth saying a few things about backwards compatibility and other versions of Excel.

If you run Excel 365 Installed desktop version, then I recommend you use @ every time you use INDEX. And especially where the formula in row 8 points to a cell with that resolves to zero or to an empty cell. If you see @INDEX in a formula built by someone else, you know why.

If you run Excel 2019 or older, then you can use INDEX. If anyone opens your file on Excel 365, then Excel will automatically convert it by adding a @ “AT-sign”.

If you run Excel 365 online version, then this principle also applies to formula. BUT, macros will not work in the online version. So our Fast Excel Development Method won’t be much use to you. In this case, install it on your machine and then use Dropbox. Or run the installed version and then sync your local files with Excel online.

If you run any version between Excel 2010 and the latest Excel 365 we will continue to support you. Everything that we do will be tested and checked for backwards compatibility. We ignore a lot of newer functions because we are aware that many of our users have older versions and we want to be inclusive.

If you run Excel 2007 or older, we can still develop custom systems for you. But we can’t guarantee that our downloads will work with your version. There are formulas that are so common now that weren’t supported (IFERROR for example) and pivot tables were built a different way in versions before 2007. I think 10 years is a reasonable timeframe to support backwards compatibility. I know there are old, gnarly Excel developers out there who insist on using vintage versions of Excel. But we have moved on to better places 😉

I would be interested in hearing from you if you have 2007 or older and have no plans to change. And want to keep using our methods. Please contact me on [email protected] or here.

If you run Excel for Mac, then right now the Fast Excel Development Template is not fully compatible with Mac OS. If you have version 2016 or older then there is support for Macros. And we are looking into what it would take to make the Development Template fully compatible. But this is work in progress for now.

You can check the compatibility of your Excel 365 file with older workbooks, using the Compatibility Checker – File > Info > INSPECT WORKBOOK > Check for Issues > Check Compatibility. This is proving to be quite a useful function. PArticularly as Windows doesn’t like multiple versions of Excel on the same computer.

We will continue to use Excel 365 Installed version and then test compatibility with Excel versions all the way back to 2010.

5 thoughts on “Our Favourite formula is INDEX. Here’s How Microsoft Changed It In 2020 With Dynamic Arrays”

  1. Michael Anzalone

    I found this information to be very confusing:

    For example, here is a list of fruit. The range of cells B11:B20 has the range and the formula =INDEX(B2:B11,D3) returns the item that is on the 6th row: Watermelons.

    First, the range of cells containing the data is not B11:B20. There’s no explanation about why the formula would return Watermelons. And there is no explanation about where the “6” came from.

    I have been using Excel since it was called “Lotus 1-2-3” (sarcasm), and have written many formulas and semi-complex macros, but I could not make any sense of this article as it is written. I had to create a spreadsheet to duplicate the data presented and finally figured it out on my own, with the help of other resources. I was disappointed in how the article failed to effectively educate me on the proper use of INDEX.

    I have no doubt that you are experts in using Excel; however, your ability to convey your knowledge in an understandable manner is not apparent in this article.

    1. Hello Michael,

      Thank you for your feedback. You are right, that part of the explanation could have been worded more clearly. And there was an error in the data range, so thank you for pointing that out. I have updated it and improved the explanation.

      I am glad that you eventually got to figure it out on your own. It would have been better for the article to do this for you without the need for further research. However, INDEX is a really powerful technique and I’m confident it will enrich your spreadsheet development.

  2. Hi Kien,
    I guess I must be an “old, gnarly Excel developer” who insists on using a vintage version of Excel!
    I still run Excel 2003 on a vintage x86 Win XP machine. Why? Because it runs WAY faster than any of the newer versions of Excel on x64 ‘super computers’. That said, now I only do that for one application which I tried to migrate to Excel 2007, but it gravely corrupted it and Excel 2010 was only marginally better, forcing me to revert to using an uncorrupted prior version in Excel 2003. Despite being an “old, gnarly Excel developer”, I have recently moved to using Excel 365. I have had to go through some very frustrating sessions nutting out how to get some applications that worked fine previously, to work properly in Excel 365. I reckon I could teach Sherlock Holmes a thing or two! Adding to the pain, Microsoft seems to enjoy changing things overnight in 365 without any hint, warning or explanation. Despite the initial pain, frustration and lost time, I have reached the point where most of the workbooks I use regularly are now adapted to operate reasonably well in Excel 365. One of the frustrations relates to command buttons: If there are more than a handful in a sheet, they all blink repeatedly and this seems to drastically slow down calculation in the sheet and macro execution. I came across a suggestion that putting all the command buttons inside a group frame helps. I tried that and found the performance improvement only marginal. I tried replacing the command buttons with a menu form containing a replica set of buttons, displayed by a single button in the sheet: It worked but was a REAL pain to setup & maintain, with inability to right-click to edit code, instead having to dive into the userform. I have since implemented a method where the command buttons in the sheet are normally hidden. They are only displayed when the mouse rolls over the area and are hidden again when the mouse leaves the area. Feels weird to use, but performance has improved substantially, so I live with it.
    I really appreciate your article above. The INDEX functionality change is something that would definitely have bitten me real bad sooner or later. I am so glad that your article title caught my attention and and my curiosity led my inside!
    Cheers,
    King (of Cookernup)
    ‘Down Under’

    1. Hello King,

      Thank you for the reply and insight into adventures in compatibility. I will certainly remember that if I ever have to deal with multiple command buttons. Your solution sounds elegant.

      I’m glad that you found the INDEX content useful and I’m pleased that the headline did the job of pulling you in without disappointing you of the contents!

      Cheers

      Kien

  3. Carlos Fernandez

    Great article!! Finally I got why the use of “@” character and why the “Spill!” appears, I will give it a try with no doubt, thanks for all the research you made and sharing the new @INDEX speed trick !!

Leave a Comment

Scroll to Top