Long Computational Time for INDEX/MATCH, Can it be reduced?

Hey Guys,

As per the title I have done a workbook for my work at when the index/match search range was around 300 cells the workbook was snappy etc, I have recently doubled the search range from 300 to 637 and the workbook is slowing down. I have posted my index match array formula here. Any help would be greatly appreciated.

=IFERROR(IF(INDEX('Proposal Register DATA 2020'!$A$37:$O$637,SMALL(IF(('Proposal Register DATA 2020'!$A$37:$A$637<=$L$30)*('Proposal Register DATA 2020'!$A$37:$A$637>=$L$29),MATCH(ROW('Proposal Register DATA 2020'!$A$37:$A$637),ROW('Proposal Register DATA 2020'!$A$37:$A$637),0),""),ROW('Proposal Register DATA 2020'!B56)),COLUMN('Proposal Register DATA 2020'!B56))="","",INDEX('Proposal Register DATA 2020'!$A$37:$O$637,SMALL(IF(('Proposal Register DATA 2020'!$A$37:$A$637<=$L$30)*('Proposal Register DATA 2020'!$A$37:$A$637>=$L$29),MATCH(ROW('Proposal Register DATA 2020'!$A$37:$A$637),ROW('Proposal Register DATA 2020'!$A$37:$A$637),0),""),ROW('Proposal Register DATA 2020'!B56)),COLUMN('Proposal Register DATA 2020'!B56))),"")

Please note that the iferror is there as it was returning an error for empty cells and the second if statement if there because the index/match was returning 0 values at times. More information would be the table is 637 by 14 giving it a total search range of 8918 cells.

Thanks for your time MrShorty, I will review that thread over my lunch break. I guess the thing that stands out to me is that I am basically repeating my index/match twice in each cell as to return a blank cell instead of a 0, of the top of your head would there be a more efficient way to get this result.

Any thanks again, any help is greatly appreciated.

Hi TMS,

Thank you for your time unfortunately the table is an array of currency, general, dates etc. see image below. Could I get around this by simply formatting the cells respectively, apologies if I'm off track I haven't much experience with linking like that.

Many thanks for your time.

Attachment 691793