Excel whole column reference in array formula too slow, not respecting UsedRange?

0
votes

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?

1
arraysexcelexcel-formulaarray-formulas
Not all Functions check the used-range. Maybe you should format your SOURCE as tabel (CTRL-T) or name the range. - Chris
@Chris It is formatted as a table, but I need to use only indirect references, because table is created dynamically with a C# program. - Vojtěch Dohnal
Which Excel-Version do you use? - Chris
If you are building the formula at run-time then you can use UsedRange.Rows.Count to build the formula dynamically. - shrivallabha.redij
To bad, with 365 you could use XLOOKUP(XLOOKUP()). I think this will be faster than INDEX(MATCH()) Array Function... - Chris

1 Answers

1
votes

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