Forum Discussion
Index/Match Array Issue
I am trying to figure out a formula for Index/Match to return the gross value for certain rows for each employee. The problem is the array for each employee can change depending on if they have or ha...
SisLilFeathers wow that is a less than ideal set up for the table. I also don't understand what you mean by "return the gross value for certain rows for each employee". But whatever it is, may be possible if you have either a list of all employees or a list of all possible categories (and of course there is no overlap). Here is an example I did of a cumulative sum of Gross for all subitems below that line until it reaches the next name:
=SUMPRODUCT($E22:$E$41*(ROW($E22:$E$41)>ROW($E22))*(ROW($E22:$E$41)<IFERROR(AGGREGATE(15,6,ROW($A23:$A$41)/ISNUMBER(SEARCH($A23:$A$41,CONCAT($J$22:$J$23))),1),ROW($A$41))))