How to SORTBY() a Spill Array

[TheMacroNoob]

Hello excel experts,

I am getting very frustrated by what appears to be a straightforward formula.

I have a dynamic array spilling vertically, and various numbers for several years found via xlookup. I want to be able to sort this data, so I made another table to grab the array of GL accounts, filter out blanks, and sort by various years (which I will have a dropdown list and if statements to do later. (IF(X="2012",SORTBY(XYZ)).

My formula is this: =SORTBY(FILTER(L5#,L5#<>""),N5:N21)
The filtered array works, checking with F9, but I don't know how to make the sort array work. I had it working previously and then it simply started giving me #VALUE! errors instead of spilling the sorted array.

Where am I going wrong?

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
2
3										Paste Codes Here
4		GL Account	Description	2012	2013	2014	2015			GL Accounts		GL Account		2012	2013	2014	2015
5		#VALUE!								673		673	Potato	$ 145,777	$ 90,753	$ 89,721	$ 13,360
6										812		812	Not Potato	$ 139,033	$ 128,391	$ 3,682	$ 77,532
7										575		575	Tomato	$ 24,808	$ 126,970	$ 56,645	$ 71,777
8										632		632	Rent	$ 133,592	$ 147,537	$ 16,978	$ 138,535
9										602		602	No Rent	$ 17,734	$ 88,404	$ 107,351	$ 129,390
10										787		787	French	$ 44,881	$ 125,199	$ 73,334	$ 106,200
11										588		588	American	$ 146,340	$ 119,617	$ 134,341	$ 3,848
12										898		898	Baked Potato	$ 19,533	$ 22,006	$ 140,263	$ 81,875
13										704		704	Frisbee	$ 51,808	$ 60,762	$ 53,752	$ 143,390
14
15
16
17
18
19
20
21
22
Sheet1

Cell Formulas
Range	Formula
B5	B5	=SORTBY(FILTER(L5#,L5#<>""),N5:N21)
L5:L21	L5	=IF(UNIQUE($J$5:$J$21,TRUE,FALSE)=0,"",UNIQUE($J$5:$J$21,TRUE,FALSE))
M5:M21	M5	=IFERROR(XLOOKUP(L5,Sheet2!B:B,Sheet2!C:C),"")
N5:Q21	N5	=IFERROR(XLOOKUP($L5,Sheet2!$B:$B,Sheet2!D:D),"")
Dynamic array formulas.

 

[Fluff]

How about

=SORT(FILTER(L5#:Q5,L5#<>""),3)

 

[TheMacroNoob]

How about

=SORT(FILTER(L5#:Q5,L5#<>""),3)

Thank you again for your quick solution, Fluff.

Your formula works even better than my intended solution.

Solved!

 

[Fluff]

Glad to help & thanks for the feedback.