I am trying to run this bit of code as an array equation:
=SUMPRODUCT(INDEX('TEST DB'!C:C,MATCH(M1&(INDEX($C:$C,MATCH(OFFSET($A$2,,,$M$4+$M$3+1)+M2-$M$4,B:B,1))),'TEST DB'!A:A&'TEST DB'!B:B,0)))
Offset A2 is a helper column from 1 to 1000, M2, 3, 4 are integers, creating an array of certain size then filling that array with a sequential set of numbers. This array then converts those numbers to text using Column B and C. I need the index and match function because the match between text and ints in the offset array are not exact (otherwise I would use a sumproduct search method). From there the next index match searches a DB (very large) of two different values, M1, and the newly created text array. This creates a single array of the sampled parameter within the DB. The sumproduct then sums up the newly sampled array.
Here is the problem. If I run this code on two cells, think highlighting A2:B2, or any array larger than 1x1, and hit Ctrl+Shift+Enter. The math is correct, and cells A2:B2, are both populated with the correct answer. If I run it Ctrl+Shift+Enter, on just a 1x1 array (single cell) it pulls just the first value of the embedded array. Why does it run correctly on a 1xn array but not a 1x1? Also, subtotal and aggregate pull just the first parameter in the array, when running it like this. Why is that?
I cant use helper columns because of the size of the workbook. This code is like 100KB vs a method with helper columns is around 20MB. I need this to scale, so trading processor time for memory is a must.
Also, we cant use VBA.
Any insight is appreciated.
=SUMPRODUCT(INDEX('TEST DB'!C:C,MATCH(M1&(INDEX($C:$C,MATCH(OFFSET($A$2,,,$M$4+$M$3+1)+M2-$M$4,B:B,1))),'TEST DB'!A:A&'TEST DB'!B:B,0)))
Offset A2 is a helper column from 1 to 1000, M2, 3, 4 are integers, creating an array of certain size then filling that array with a sequential set of numbers. This array then converts those numbers to text using Column B and C. I need the index and match function because the match between text and ints in the offset array are not exact (otherwise I would use a sumproduct search method). From there the next index match searches a DB (very large) of two different values, M1, and the newly created text array. This creates a single array of the sampled parameter within the DB. The sumproduct then sums up the newly sampled array.
Here is the problem. If I run this code on two cells, think highlighting A2:B2, or any array larger than 1x1, and hit Ctrl+Shift+Enter. The math is correct, and cells A2:B2, are both populated with the correct answer. If I run it Ctrl+Shift+Enter, on just a 1x1 array (single cell) it pulls just the first value of the embedded array. Why does it run correctly on a 1xn array but not a 1x1? Also, subtotal and aggregate pull just the first parameter in the array, when running it like this. Why is that?
I cant use helper columns because of the size of the workbook. This code is like 100KB vs a method with helper columns is around 20MB. I need this to scale, so trading processor time for memory is a must.
Also, we cant use VBA.
Any insight is appreciated.
