#### Twollaston

##### Board Regular

- Joined
- May 24, 2019

- Messages
- 235

**Additional Info:**It works fine if I do 1080 rows at a time. These formulas are basically dynamic lists, that populate based on a pivot table connected to power BI and then I set up like 4000 named ranges so that I can sumproduct multiple arrays of data. I'm running Excel Version 2010.

**My Formulas:**

Cell I2

=IFERROR(INDEX($E$1:$E$32401,AGGREGATE(15,3,(($A$1:$A$32401=$H2&" - "&LEFT(J$1,2))/($A$1:$A$32401=$H2&" - "&LEFT(J$1,2))*ROW($A$1:$A$32401)),$G2)),"")

Cell J2

=IFERROR(INDEX($D$1:$D$32401,AGGREGATE(15,3,(($A$1:$A$32401=$H2&" - "&LEFT(J$1,2))/($A$1:$A$32401=$H2&" - "&LEFT(J$1,2))*ROW($A$1:$A$32401)),$G2)),"")

Cell K2

=IFERROR(INDEX($E$1:$E$32401,AGGREGATE(15,3,(($A$1:$A$32401=$H2&" - "&LEFT(L$1,2))/($A$1:$A$32401=$H2&" - "&LEFT(L$1,2))*ROW($A$1:$A$32401)),$G2)),"")

Cell L2

=IFERROR(INDEX($D$1:$D$32401,AGGREGATE(15,3,(($A$1:$A$32401=$H2&" - "&LEFT(L$1,2))/($A$1:$A$32401=$H2&" - "&LEFT(L$1,2))*ROW($A$1:$A$32401)),G2)),"")

Cell M2

=IFERROR(INDEX($E$1:$E$32401,AGGREGATE(15,3,(($A$1:$A$32401=$H2&" - "&LEFT(N$1,3))/($A$1:$A$32401=$H2&" - "&LEFT(N$1,3))*ROW($A$1:$A$32401)),$G2)),"")

Cell N2

=IFERROR(INDEX($D$1:$D$32401,AGGREGATE(15,3,(($A$1:$A$32401=$H2&" - "&LEFT(N$1,3))/($A$1:$A$32401=$H2&" - "&LEFT(N$1,3))*ROW($A$1:$A$32401)),G2)),"")

Cell O2

=IFERROR(INDEX($E$1:$E$32401,AGGREGATE(15,3,(($A$1:$A$32401=$H2&" - "&LEFT(P$1,3))/($A$1:$A$32401=$H2&" - "&LEFT(P$1,3))*ROW($A$1:$A$32401)),$G2)),"")

Cell P2

=IFERROR(INDEX($D$1:$D$32401,AGGREGATE(15,3,(($A$1:$A$32401=$H2&" - "&LEFT(P$1,3))/($A$1:$A$32401=$H2&" - "&LEFT(P$1,3))*ROW($A$1:$A$32401)),G2)),"")