How to rank using index,match, small function

[Msigwa]

Hi
Im trying to rank some sort of data, the index formula worked fine when there is no blank cell in AZ, and AY.

But when there is blank cell the formula can't work. Below is the formula i used
=INDEX(J$44:J$1772,MATCH(SMALL(INDEX(AZ$44:AZ$1772+(AY$44:AY$1772/10),0),ROWS(J$44:J44)),INDEX(AZ$44:AZ$1772+(AY$44:AY$1772/10),),0))

 

[Domenic]

I have amended your formula so that it only ranks rows where both Column AZ and Column AY contain numbers...

=INDEX(J$44:J$1772,MATCH(SMALL(IF((ISNUMBER(AZ$44:AZ$1772)+ISNUMBER(AY$44:AY$1772))=2,INDEX(AZ$44:AZ$1772+(AY$44:AY$1772/10),0)),ROWS(J$44:J44)),INDEX(AZ$44:AZ$1772+(AY$44:AY$1772/10),0),0))

Hope this helps!