Trying to Sort by two different columns while using the FILTER function.

Hi @Wright, Paul@CHP,

Thank you for posting your question in the Microsoft Q&A forum.

Based on your sharing, the reason you had #VALUE! error is because the structure of SORTBY formula is: SORTBY(array, by_array1, sort_order1, [by_array2, sort_order2], ..) and it needs each array part is in the same height as the array you're sorting. In your formula:

• You use FILTER to returns the rows that meet E>44 and F>44. I assume about 16 rows meet that condition
• The sort keys you use are Q9:Q38 and K9:K38 which are always considered as 30-row ranges
• That's why when SORTBY tried to sort the 16-row array using 30-row by_array keys, the height is different so the function returns error.

Therefore, you need to point your sort keys to columns inside the filtered array. I suggest you can try this formula:

=LET(f,FILTER('Score Calculation'!B9:Q38,('Score Calculation'!E9:E38>44)*('Score Calculation'!F9:F38>44),""),SORTBY(f,INDEX(f,,16),-1,INDEX(f,,11),-1))

Hope this information proves helpful. You can try this formula and let me know if it works for you. If not, we can work together to find a solution.

Thank you for your understanding and cooperation. If you have any questions or need further assistance, please feel free to share them in the comments so I can continue to support you.

I'm looking forward to your reply.

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".      

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.