Order array by value using formula

[ddoublev2010]

Hi,

Im trying to set up some dynamic sorted ranges.
The top section are array formulas searching a large table of data for records that match given criteria. This part is working fine
The bottom section is supposed to sort the names by value largest to smallest.
As shown on the left it works as long as the range it is searching includes data in all cells
On the right it returns an error, have tested by reducing the range to only cells with data and it works perfectly.

The formula in P22 is:
=INDEX($P$16:$P$20,MATCH(LARGE($Q$16:$Q$20+ROW($Q$16:$Q$20)/1000,O22),$Q$16:$Q$20+ROW($Q$16:$Q$20)/1000,0))

Any ideas how I can do this with ability to ignore blanks/errors?

All assistance is appreciated

 

[KRice]

When you reentered the formula, did you confirm with Ctrl-Shift-Enter? I didn't get the same error that you describe.

 

[shaowu459]

for your reference.
if you need descending order, change the last 1 to -1.

新建Microsoft Excel 工作表.xlsx
	S	T	U
15		Helm	Value
16	1	A	0.616665
17	2	B	0.478017
18	3	C	0.096805
19	4	D	0.202108
20	5
21
22	1	C
23	2	D
24	3	B
25	4	A
26	5
Sheet1

Cell Formulas
Range	Formula
T22:T26	T22	=INDEX(SORT(T16:U20,2,1),,1)&""
Dynamic array formulas.

 

[jasonb75]

I agree with @KRice that you shouldn't be getting the error if the formula is entered correctly (except for the last row where it will show the error because there is no match or function to catch the error).
@shaowu459 has provided an alternative method for you which will only work with up to date installations of office 365, I've added another one for you that will work with all versions from 2010 onwards.

Book1 (version 1).xlsb
	O	P	Q
16	1	Dornish	0.30176
17	2	Stark	0.27522
18	3	Stark Relics	0.36736
19	4	Whent	0.30176
20	5
21
22	1	Stark Relics	0.36736
23	2	Dornish	0.30176
24	3	Whent	0.30176
25	4	Stark	0.27522
26
Sheet1

Cell Formulas
Range	Formula
O22:O26	O22	=IF(ROWS(O$22:O22)>COUNT($Q$16:$Q$20),"",ROWS(O$22:O22))
P22:P26	P22	=IF(O22="","",INDEX($P:$P,AGGREGATE(15,6,ROW($P$16:$P$20)/($Q$16:$Q$20=Q22),COUNTIF(Q$22:Q22,Q22))))
Q22:Q26	Q22	=IF(O22="","",LARGE($Q$16:$Q$20,O22))

 

[ddoublev2010]

Thankyou very much for your assistance, for some reason the array formulas were not working, the solution from Jason has worked perfectly
Thanks again