Forum Discussion
Excel 2010 workaround for 365 FILTER & SORT functions.
Windows 10 – 64 bit MS Office 2010 – Version: 14.0.7268.5000 (32-bit) I am looking for a workaround for the FILTER and SORT functions on 365 so that I can have comparable features in the outdate...
Without dynamic arrays, one of the go-tos is INDEX with a nested SMALL-IF array to determine row positions to pick through relevant rows from the top-down. It's not recommended for large data sets because the formula would have to be applied to cells in anticipation of data maybe being returned. Sorting on top of this using an array might be theoretically possible but one might expect many computers to sound like a cement mixer trying to perform the calculation. Not recommended in 2010.
Here's an example of the INDEX-SMALL array (Ctrl+Shift+Enter to define formula as an array):
=IFERROR(INDEX(A$1:A$11,SMALL(IF(Active="Y",ROW(Active)),ROW(A1)),1),"")