There are some array features in spreadsheets which recently have been added to Excel. These functions make lots of sense and they remind me of statistical software (e.g. Stata) which have very similar functions:
FILTER, SORT, SORTBY, UNIQUE, SEQUENCE, RANDARRAY
Steps to Reproduce:
User Profile Reset: No
Note that those functions aren't fully specified yet, for example https://support.office.com/en-us/article/RANDARRAY-function-21261e55-3bec-4885-86a6-8b0a47fd4d33 says:
Note: January 14, 2019: The RANDARRAY function is one of several beta features, and currently only available to a portion of Office Insiders at this time. We'll continue to optimize these features over the next several months. When they're ready, we'll release them to all Office Insiders, and Office 365 subscribers.
Also, it needs to be investigated how the spilling situations are actually handled and what happens if due to recalculation the return array size varies. This is not just new spreadsheet functions but a new behaviour that calculating a function can change spreadsheet content.
Also restrictions like such formula can be edited only at the top left of its array need to be implemented, though we maybe could handle that differently as long as the edited formula affects the range as if it was edited at the top left. Our current array formula display/edit behaviour already is somewhat similar.
*** Bug 127809 has been marked as a duplicate of this bug. ***
I would like to support Gerry's cause.
In my opinion also the XLOOKUP function would be very helpful. It returns an array if needed.
XLOOKUP, FILTER, SORT, UNIQUE
are my wishes.