# Help with Rank

#### Silo

##### Active Member
Hello Everyone

I need help with a ranked formula.

Here’s what I need.

I like to rank values in I3:I19 in descending order, with there corresponding Text Value (Employee Names) which are in range A3:A19.

I like this ranking in Columns L or M starting in the 3rd row.

Any help would be appreciated

Thanks

### Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result
Try this array formula

=MATCH(I3,LARGE(IF(\$A\$3:\$A\$19=A3,\$I\$3:\$I\$19),ROW(INDIRECT("1:"&COUNTIF(\$A\$3:\$A\$19,A3)))),0)

xld

I try that formula with no success

Is there something else I need to do to this formula?

Did you array enter it (Ctrl-Shift-Enter)

How do I do that?

Select the cell with the formula - select F2 - select Ctrl + Shift + Enter

Yes, I copied the the rest of the cell in the range where I need them ranked L3:L19 and they returned 1

I must be doing something wrong

Yes, then I copied the formula to the rest of the cell's in the range I need L3:L19.

Returns a 1 in each cell.

I need the Text Values in range A3:A19 and the values in I3:I19 in decending order.

Bacislly I like to know by Name, Joe and his Units sold Joe 145 and so on by employee and their repective value in decending order.

Thanks everyone

Hello Silo,

So in A3:A19 you have 17 different names and you want to list them in order depending on the values in I3:I19?

I'd use a "helper" column, e.g. in L3 copied down to give a "unique rank"

=RANK(I3,I\$3:I\$19)+COUNTIF(I\$3:I3,I3)-1

Then in M3 copied down to give names in order

=INDEX(A\$3:A\$19,MATCH(ROWS(M\$3:M3),L\$3:L\$19,0))

and associated units sold in N3 copied down

=INDEX(I\$3:I\$19,MATCH(ROWS(M\$3:M3),L\$3:L\$19,0))

hide column L if you wish......

Replies
14
Views
284
Replies
2
Views
174
Replies
25
Views
769
Replies
8
Views
319
Replies
15
Views
732

1,207,091
Messages
6,076,523
Members
446,212
Latest member
KJAYPAL200

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back