Better way to index

I record expense reports monthly by totaling the sum for each employee. An indexing formula worked fine until we began to grow, and now the same formula takes a long time and locks up Excel for a period while returning results. Can you provide a better formula or method to obtain the same result? I uploaded a sample of what we're using now.

Regards,
Gary

In G2 entered as an array:

=INDEX(E$2:E$1000,MATCH(0,COUNTIF(E$2:E$1000,"<"&E$2:E$1000),0))


In G3 entered as an array, then copied down about 40-50 rows:

=IF(COUNTIF(E$2:E$1000,">"&G2), INDEX(E$2:E$1000, MATCH(COUNTIF(E$2:E$1000,"<="&G2), COUNTIF(E$2:E$1000,"<"&E$2:E$1000),0)),"")


I also fixed the other formulas...in H2 entered as a normal formula and copied down:

=IF(G2="","",SUMIF(E:E,G2,C:C))

In I2 and copied down:

=IF(G2="","",COUNTIF(E:E,G2))

Given the nature of your data and the fact you want to do this as efficiently as possible with formulae (rather than using a Pivot) I'd suggest:

Please Login or Register  to view this content.

The LOOKUP above assumes that your data is sorted by Account Name which would seem to be the case given sample - used in this form it is an extremely efficient formula (binary search)

Calculation time for your matrix using the above would be around 0.002 seconds.

Glad to help.

If that takes care of your need, please click EDIT in your original post, click GO ADVANCED and set the PREFIX box to SOLVED.