Hi!
I've been trying to use Excel formulas (No VBA) to mimic the behavior of nested "for" loops. I want to create a table of calculated values, where the values are determined by filtering on the column label and row label.
Values are pulled from a filtered dynamic array using: filter(choosecols(array, values), (choosecols(array, year) = row label) * (choosecols(array, employee) = column label))
However, since both the row and column labels are also arrays of dynamic length, I want to use a single array-friendly function to capture both the row and column labels instead of duplicating/deleting formulas from each row/column when the label lengths change.
I tried using map(row label array, column label array, lambda(....)), but the formulas only work when just one array is passed through this method.
I know this can be easily accomplished through pivot tables, but I'd like to avoid them as my original file size is quite large. Would appreciate any pointers, thanks in advance! File attached has an example of my attempted formula with calc. errors, and an example of when my formula only works with one label array.
Bookmarks