If an Excel function requires a ref and will not take an array, is there a way to convert an array to a ref?

[TomCon]

For instance, i can use this array formula with the SUM() function.

=SUM(IF(N2:N1037="Relief",L2:L1037))

But, if i want to use the RANK() function, i analogously try:

=RANK(L2,IF(N2:N1037="Relief",L2:L1037))

This only gives #VALUE. As the function RANK requires a ref.

So, is there a way i can accomplish the goal with the RANK function, of filtering the values in L2:L1037, to reduce them in number, and then get the ranks based on that filtered array? Somehow convert the array described by

IF(N2:N1037="Relief",L2:L1037)

to a ref, or something suitable to the RANK function?

Thank you.!

 

[Eric W]

You can't turn an array into a reference. It's frustrating that some functions, like RANK, don't allow arrays. I know of no reason why it shouldn't work. So you have to work around it, something like:

Book2
	L	M	N
1
2
3	1	Relief	4
4	4	x
5	6	Relief	2
6	3	Relief	3
7	8	x
8	7	Relief	1
9
Sheet5

Cell Formulas
Range	Formula
N3:N8	N3	=IF(M3="Relief",MATCH(L3,SORT(FILTER($L$3:$L$8,$M$3:$M$8="Relief"),1,-1),0),"")

You can change the -1 to a 1 if you want the ranking to be in ascending order.