I have a worksheet with many array formulas referencing another small source data sheet with a couple of rows. Formulas are as follows:
{=INDEX(Source!$F:$F,MATCH(1, (Source!$A:$A=$B6)*(Source!$B:$B=H$1),0))}
The calculation runs very slowly. When I replace the whole column references with ranges of 1000 cells, the workbook calculation speeds up significantly.
{=INDEX(Source!$F$1:$F$1000,MATCH(1, (Source!$A$1:$A$1000=$B6)*(Source!$B$1:$B$1000=H$1),0))}
I have chcecked the UsedRange on the Source sheet and it is OK and just a few rows (using ctrl+end). Using whole column reference is quite normal practice but it does not really work well here, since it does not respect UsedRange.
Found an article on this topic: https://fastexcel.wordpress.com/2015/12/12/excel-full-column-references-and-used-range-good-idea-or-bad-idea/
that says:
But avoid array formula and SUMPRODUCT usage of whole column reference
But why? I cannot understand the reason why Excel is not capable to limit the calculation algorithm of array formulas to the used range? Is there any other workaround?
Not sure if you are building this through VBA but if it's just formulas you are using you could try something like:
=INDEX(Source!$F$1:$F$1000,MATCH(1,INDEX((Source!$A$1:INDEX(Source!$A:$A,COUNTA(Source!$A:$A))=$B6)*(Source!$B$1:INDEX(Source!$B:$B,COUNTA(Source!$B:$B))=H$1),),0))
Looks lengthy, but it basically is a dynamic way of limiting the column references to the last used row. Btw, another great tip done by @Chris in the comments is the use of an actual table instead which should speed things up even more.
Btw, you won't have to enter this through CtrlShiftEnter nomore due to the second INDEX