Index Match Index Formula work slow

Hellow friends,
I have using Excel 2007.

I creating two sheets. (Sheet1 & DATA)

In DATA sheet : Total 20,000 row record. (sorted : column "C" by order A to Z & column "B" by order newest to oldest) column "C" is code & "B" is date.

In Sheet1 : I have calculate customer wise recorder. i.e. number of quantity sold & last date of transaction.

for example : In F6 position I enter formula =SUMIF(DATA!$AI:$AI,":"&$A6&"::"&$F$2&":",DATA!$N:$N)
In Sheet1 : A6 is code F2, G2, H2, I2, J2 is series.
In DATA sheet AI column created a key with two columns (=":"&C2&"::"&Q2&":") code & series & column "N" quantity.

Same formula in column : G6,H6,I6,J6
=SUMIF(DATA!$AI:$AI,":"&$A6&"::"&$G$2&":",DATA!$N:$N)
=SUMIF(DATA!$AI:$AI,":"&$A6&"::"&$H$2&":",DATA!$N:$N)
=SUMIF(DATA!$AI:$AI,":"&$A6&"::"&$I$2&":",DATA!$N:$N)
=SUMIF(DATA!$AI:$AI,":"&$A6&"::"&$J$2&":",DATA!$N:$N)

Now
in column K6 i using formula: =INDEX(DATA!$B:$B,MATCH(1,INDEX(($A6=DATA!$C:$C)*(Sheet1!$K$2=DATA!$Q:$Q),0),0))
in column L6 i using formula: =INDEX(DATA!$B:$B,MATCH(1,INDEX(($A6=DATA!$C:$C)*(Sheet1!$L$2=DATA!$Q:$Q),0),0))
in column M6 i using formula: =INDEX(DATA!$B:$B,MATCH(1,INDEX(($A6=DATA!$C:$C)*(Sheet1!$M$2=DATA!$Q:$Q),0),0))
in column N6 i using formula: =INDEX(DATA!$B:$B,MATCH(1,INDEX(($A6=DATA!$C:$C)*(Sheet1!$N$2=DATA!$Q:$Q),0),0))
in column O6 i using formula: =INDEX(DATA!$B:$B,MATCH(1,INDEX(($A6=DATA!$C:$C)*(Sheet1!$O$2=DATA!$Q:$Q),0),0))

In data sheet column B is date, column C is code, column Q is series.
In sheet1 : column A6 is code, column K2,L2,M2,N2,O2 is series.
In sheet1 total data row is approx 3000.

My problem is : when i selecting filter & selecting any type of data i.e. > or < or any data record. it will take calculating / processors time more than 15 to 20 minutes its work very slow.

Is there a way to faster calculation.

I have attach sample file with some record.

Hi

change in formulas the ranges like $B:$B into real range with data $B$1:$B$38 (the same with Q and C column) it will help

So you can always change the ranges to smaller e.g. 1000 rows (bigger than your max weekly rapport with records). Every formula in your sheets works on 1 048 576 rows (when You use filter it is recount in my opinion and this is a cause of slow down)
Beside changing the range in formula is easy (you simply drag it down)
other solution could be use of macro which find the correct values without formula.

Originally Posted by avk

Hi
If i changed real range, then every month necessary to change real range every time. That i don't want. Plz. note that, every week data sheet update with new record. Give any other solution.

Using XL2007 you can use the Table feature and refer to that table in your formulas. he table will automatically adapt when rows are added/deleted.

Click in you data sheet - Insert Tab - Table You can change the table's name at your convenience

Hi try this code (run this with sheet1 activate - or better create the button on this sheet)
It will make the same what your function with index did.
p.s it is not fast but all the problem with filter should disapear

Please Login or Register  to view this content.

Try the attached workbook.

1/. It's probably the SUMIF() that is pulling the performance back
Try this dynamic formula in Sheet1!F4

Please Login or Register  to view this content.

This will dynamically only sum the exact number of rows used, based on the last date entered in column DATA!$B:$B


2/. I don't think that you are returning the last dates in Sheet1 K:N your formula returns the date of first match found not the last date.
Try this array formula

Please Login or Register  to view this content.

Confirm with Ctrl+Shift+Enter not just Enter
With 2007 and above this could be

Please Login or Register  to view this content.