# Index Match Index Formula work slow

1. ## 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.  Register To Reply

2. ## Re: Index Match Index Formula work slow

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  Register To Reply

3. ## Re: Index Match Index Formula work slow

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.  Register To Reply

4. ## Re: Index Match Index Formula work slow

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.  Register To Reply

5. ## Re: Index Match Index Formula work slow 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  Register To Reply

6. ## Re: Index Match Index Formula work slow

I changed real range & after selected filter data record, its take less than 1 minute.
Secondly plz. let us know to how use macro to find correct values without formula. Plz. note that, i am not familiar with VB. If you give with example, so that i using with our other huge data file with necessary changes.Thanks.  Register To Reply

7. ## Re: Index Match Index Formula work slow

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.``  Register To Reply

8. ## Re: Index Match Index Formula work slow

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.``  Register To Reply

9. ## Re: Index Match Index Formula work slow

Dear TOM, Firstly sorry for delay reply from our side. Yes I used your micro & found that the work done perfectly. Thanks.  Register To Reply

10. ## Re: Index Match Index Formula work slow

Marcol, Sorry for delay reply. This is also helpful. Thanks.  Register To Reply

##### Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1