The formula =INDEX(SORT(B2:TL80000,531,1),SEQUENCE(50),{1,2,3,5,6,531}) correctly returns 50 rows of six columns from a very large array (over 60,000 rows and hundreds of columns of data). It gives me the top 50 sorted smallest to largest. BUT when I change the "1" to a "-1" (in the SORT function) to get the top 50 sorted largest to smallest it returns many #N/A and no numbers. I need a simple way to modify this formula to ignore any #N/A's in the data.
Any ideas? (Excel 365)
Bookmarks