SOLVED

Are CHOOSECOLUMNS() , TAKE(), DROP() etc faster than INDEX(), FILTER() to return selective columns ?

Contributor

Hi, so I've made multiple worksheets in the past where when I had to return a particular columns from the spilled array. I either used INDEX() when the required column was dynamic

Eg INDEX(array#,,columns(array#)-2)

or if I knew the columns won't expand
FILTER(array#,{1,0,1,1,0}

Since my sheets are getting heavy, I wanna cease every opportunity to
micro influence the total speed. Are the new functions (take, drop,choosecolumns) faster than index,
filter method?
Should I re-do all my formulae?

@Sergei Baklan @Riny_van_Eekelen @Peter Bartholomew 


2 Replies
best response confirmed by Nishkarsh31 (Contributor)
Solution

@Nishkarsh31 

I didn't have any data to judge the answer to your question since I tend not to drive my workbooks to the point of collapse.  However, I have now run a couple of test cases using Charles Williams's timing routines.

 

Extracting half a million values from the middle of a 1,000,000 by 1 array took ~130 ms using INDEX/SEQUENCE and ~80 ms with TAKE/DROP.

 

More closely related to your problem, extracting columns 2 and 5 from a 1,000,000 by 5 array took ~720 ms using INDEX/SEQUENCE and ~500 ms with CHOOSECOLS.

 

So, it looks as if traditional formulas take about half as long again as the newly implemented array formulas as well as the newer functions improving readability.