Excel getting slow with INDEX array formula

Hello ,

I am getting very slow response ( Calculating ( 4 processor (s) ) when using the below formula

Array formula

=IFERROR(INDEX(MASTER!A$2:A$25000,SMALL(IF(MASTER!$B$2:$B$25000=$L$3,ROW(MASTER!$A$2:$A$25000)-ROW(MASTER!$A$2)+1),ROWS($A$2:$J2)),0),"")

Any help to speed the process.

Thank you.

Post a SMALL file so we can see the context of your formula.

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate.

Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on "Reply" then GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

Attached is a sample file.

1) Data is stored in "MASTER" WorkSheet
2) Search is done in "Search" Worksheet , entry for search is in A3

Thank you.

You can test the data by :-
1) changing the Data values in "MASTER" WorkSheet A2:A9 ( e.g 20 , 21 , 24 ) so that in the "Search" Worksheet you can see the processing speed.
2) If you leave A3 blank in the "Search" Worksheet the result will be blank.

Thank you.

Try Ford's approach( I was thinking of "helper" column(s) as a possible solution) and let us know the outcome.

Originally Posted by FDibbins

With that much data, and that many ARRAY formulas, Im not surprised your file is slow.

Here is another approach which should speed things up for you, using a helper column in Master.

1. Remove AL of those ARRAY formulas
2. Master K2=IF(A2=Search!$A$3,MASTER!K1+1,MASTER!K1)
copied down
3. Search A5=INDEX(MASTER!A:A,MATCH(ROWS($A$1:A1),MASTER!$K:$K,0))
4. Turn off manual calc
copied down and across as needed

Thanks , I am still getting slow response, maybe, because i did not understand item 1 and 4 of your post.

Can you please explain :-

For Item 1 = I removed the Array and save it as normal formula ( is this correct ?)
For Item 4 = Can you explain how to tun off the manual calc.

Originally Posted by FDibbins

With that much data, and that many ARRAY formulas, Im not surprised your file is slow.

Here is another approach which should speed things up for you, using a helper column in Master.

1. Remove AL of those ARRAY formulas
2. Master K2=IF(A2=Search!$A$3,MASTER!K1+1,MASTER!K1)
copied down
3. Search A5=INDEX(MASTER!A:A,MATCH(ROWS($A$1:A1),MASTER!$K:$K,0))
4. Turn off manual calc
copied down and across as needed

Thanks , I am still getting slow response, maybe, because i did not understand item 1 and 4 of your post.

Can you please explain :-

For Item 1 = I removed the Array and save it as normal formula ( is this correct ?)
For Item 4 = Can you explain how to tun off the manual calc.

Originally Posted by FDibbins

All Im saying is to delete ALL of those formulas you are using, then, once you gave added my suggested formulas, you can turn auto-calc back on (it is OFF in your file)


on the Formula tab/Calculation/Calculation Options, select Auto

OK thanks , I did remove all formulas and used yours.
I will turn ON the auto calc too.
Thanks.

It did almost 80% speed improvement , thank you.

But I am getting the #N/A in the blank cells , is there any way I can leave this blank for no match values

It did almost 80% speed improvement , thank you.

But I am getting the #N/A in the blank cells , is there any way I can leave this blank for no match values

I just figured out to use the IFERROR command .
Thank you very much for your help.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

Hello,

Tried to upload a test file , but getting upload error :-

TEST3.xlsm - Your file of 1.24 MB bytes exceeds the forum's limit of 1,000.0 KB for this filetype.

What should i do?

Try ZIPping the file OR simply reduce the number of rows: generally 20-30 rows is sufficient.

Originally Posted by JohnTopley

in L5

=IFERROR(INDEX(MASTER!$A$2:$A$1000,SMALL(IF(MASTER!$B$2:$B$1000=Search!$L$3,ROW(MASTER!$A$2:$A$1000)-ROW($A$2)+1,""),ROWS($A$2:$A2))),"")

Enter with Ctrl+Shift+Enter

Copy down

Change highlighted range to correct ranges in "Master" for the other columns in SEARCH


.... and I don't know why you needed to split these files.


Just re_read you initial post: the answer I gave does not provide sorted data.

Why not simple Custom Sort by the date/time fields???

See "TEST3_SORTED ("Master")

Thank you Sir,

It Works, Really appreciated, I Did Sorted the data and results are awesome.

Glad it gives you the result you required.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.