Formula for looping through two dynamic arrays at once

[excelwrld]

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.

The row labels and column labels are derived using unique() on columns from filtered dynamic array.

The table values should be 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. In the attached picture you can see my full formula in the formula bar.

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!

 

[Georgiboy]

Perhaps the below will do it:

=LET(d,H3#,
y,TRANSPOSE(UNIQUE(INDEX(d,,2))),
e,UNIQUE(INDEX(d,,1)),
s,SUMIFS(INDEX(d,,3),INDEX(d,,1),e,INDEX(d,,2),y),
HSTACK(VSTACK("",e),VSTACK(y,s)))

 

[Fluff]

I tried using map(row label array, column label array, lambda(....))

What version of Excel are you using, as your profile shows 2021 which does not have the Lambda functions or choosecols?

 

[Fluff]

Welcome to the MrExcel Message Board!

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: Formula to mimic nested "for loops" to loop through two dynamic arrays
There is no need to repeat the link(s) provided above but if you have posted the question at other places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.