I am now using a lot of array formulas like FILTER and UNIQUE, but the challenge is how do you ensure any helper formulas go down enough rows to cover all results returned by the array formula?
For instance:
Column A is a list of names., Column B is =UNIQUE(A:A), Column C is an XLOOKUP looking up each unique name from B:B in another sheet.
Right now there are 50 unique names, but in the future there could be many more. My current strategy is to fill the helper formulas like column C to a large number of rows (maybe 999 in this case) and wrap in an IF statement to return a blank result if there is no data in column B for that row.
The problems are
1. There are lots of column C calculated cells only calculating in case there is ever more data in the array, which unnecessarily slows the workbook
2. If the array exceeds the number of helper rows created, the formulas might break/return incorrect results without you knowing.
Right now I am adding doing a check for each column using COUNTA to ensure that there are the same number of non-blank rows to catch #2, but it's not ideal.
Does anyone have a suggestion? It would be amazing if there was a way for instance to wrap a function into an array that match the number of rows in column B, so that it expands and contracts as the original UNIQUE array formula does.
Hi Raymond,
Great to see you're making use of the new dynamic array functions. You should be referencing the spilled results of dynamic array formulas using the spilled array hash operator. This will ensure any growth/contraction in the spilled results is automatically included.
Mynda
This is exactly what I was looking for, thanks Mynda!
