Find 1st, 2nd and 3rd place...

[canonelan2]

We're doing a "Biggest Loser" competition at work. I have a spreadsheet that calculates the % of body weight lost.

I need a formula that will look at the results and display 1st, 2nd, 3rd places.

Here is this weeks results...

Please Login or Register  to view this content.

Here's the output I want...

Please Login or Register  to view this content.

The lowest number (negative) is the current #1 winner.

Because the lowest number is the best, I can use =INDEX($P$22:$P$30,MATCH(MIN($P$22:$P$30),$P$22:$P$30,0))
for the #1 percentage

and

=INDEX($P$22:$P$30,MATCH(MIN($P$22:$P$30),$P$22:$P$30,0))
for the last place percentage but how can I get 2nd and 3rd place?

Thanks!

[DonkeyOte]

You can use the SMALL function with k argument set to 1,2,3 to return 1st, 2nd, 3rd etc..., however, returning names can be a little more tricky if it's possible to have 1+ person with the same % loss... if not and you assume the %'s are in C you can use:

=VLOOKUP($Cx,$P:$Q,2,0)
(where x = row number)

to return the associated name.

[canonelan2]

I understand the issue if there is a tie. That worked for what I needed it to do.

Thanks a lot!

[zohaibh]

i want a forumula in result sheet where 1st , 2nd 3rd, ..... would be according to % and other thing i want to know how i manage them in order to 1t, 2nd ......... from 1st row to last

[oeldere]

Or an solution with an pivot table.

See the attached file.

[DonkeyOte]

This thread is nearly four years old.

@zohaibh,

Your post does not comply with Rule 2 of our Forum RULES. Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread. It makes sense to have a new thread for your question because a thread with numerous replies can be off putting & difficult to pick out relevant replies.

@oeldere,

In light of above please refrain from posting in these instances and advise OP accordingly.