As the above example was to demonstrate the syntax and behavior of Spill Range references, let’s look at a practical example of this ability.
We have a table that occupies cells A1 through D20 and consists of the following data:
NOTE: To keep the syntax to a minimum, we will not “upgrade” this table to a proper Excel Table. In the real world, converting this to a proper Excel Table would be considered a Best Practice.
We have provided the user with a drop-down list (via Data Validation) that allows them to choose between “Division”, “Region”, and “App.
When they select from the drop-down list, we use that choice to select one of the table’s columns and run a UNIQUE operation on that column’s data.
If you are interested in the formula used to build the list of unique values based on the drop-down selection, here is the formula.
=SORT(UNIQUE(INDEX(A2:C20, , XMATCH(G1, A1:C1) ) ) )
This formula uses the SORT, UNIQUE, INDEX, and XMATCH functions to generate a unique, sorted list.
XMATCH is a new function that is intended to replace the legacy MATCH function.
Create a Spill Range Reference with #
The practicality of # can be seen when we generate a list of row numbers for our list of user-selected items.
When we change the drop-down selection, the G2# spill reference counts the number of returned items and creates a list of numbers of the same length.