How to Refer to a Column in a Spill Range

INDEX rules

A spill range is the result of a dynamic array formula. At the moment that requires the subscription version of Excel.

An example of a spill range is shown below.

Cell E1 extracts the unique entries from the table on the left, which is the whole table. The whole table is returned and that is the spill range.

You can refer to the whole spill range by using

E1#

You can see an example below where we add up all the cells in the spill range.

How do you refer to a specific column in the spill range?

Let’s say we want to only add up the second column F1:F12.

The easy way is to use the INDEX function.

In the image below the formula in cell I2 is

=SUM(INDEX(E1#,,2))

I added some check totals in row 14 so you can see the results.

Normally the INDEX function has a row number a column number, but if you omit the row number, as I did above, it refers to the whole column within the table.

Please note: I reserve the right to delete comments that are offensive or off-topic.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.