Formula to mimic nested "for loops" to loop through two dynamic arrays

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.

You can use "map" function, you should use O365 version.

If you use O365 version, you can try this.

Please Login or Register  to view this content.

Thank you!! This works great

You are welcome.

PS. update your profile to O365.

-----------------------------------
If that takes care of your original question, please click on "Thread Tools" from the menu link above and mark this thread as SOLVED.

Or, you can simply use the SUMIFs Function, =SUMIFS($J$3:$J$26,$H$3:$H$26,B3#,$I$3:$I$26,C2#).