Populate a descending list of values with the corresponding name

I am trying to populate a descending list of values with the corresponding person's name, but I keep coming up with an error with the people's names that I can't seem to figure out. I have attached the test sheet with the formulas (see column A, D, and G) and if someone could point out where my mistake is, I would appreciate it.

Thanks!

Question:

Can you explain why A5 is Person 16? Should A5 be the person based off of the B5 value?

Should A5 return Person 19 and A6 return Person 3 based on B5 and B6?

All formulae changed. Name:

=IFERROR(INDEX('Rankings - Data'!$A:$A,SMALL(IF('Rankings - Data'!$C$2:$C$21=Rankings!B5,ROW('Rankings - Data'!$C$2:$C$21)),COUNTIF(Rankings!$B5:B5,Rankings!B5))),"")

Amount:
=IFERROR(1/(1/LARGE(INDEX(('Rankings - Data'!$B$2:$B$21=$I$1)*('Rankings - Data'!$C$2:$C$21),0),ROWS(B$5:B5))),"")

The first one is an array formula. Have you upgraded to anything more recent than 2007? If so, a non-array solution is also easy.

oops. Array Formulae are a little different from ordinary formulae in that they MUST be confirmed in the FIRST CELL ONLY by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. After that, the array can be dragged down as normal, to cover the desired range.

You will know the array is active when you see curly brackets { } - or "curly braces" for those of you in the USA, or "flower brackets" for those of you in India - appear around the outside of your formula. If you do not use CTRL+SHIFT+ENTER you will (almost always) get an error message or an incorrect answer. Press F2 on that cell and try again.

Don't type the curly brackets yourself - it won't work...

I see you have marked this as solved, but did not think to comment.

You're welcome.

---

It'd be appreciated if you were to click the Add Reputation button at the foot of any of the posts of all members who helped you reach a solution.

Hi Glenn,

Not sure what I'm missing, but in this attachment, why won't the formula in column N give all the same results as column A. One row, row 7 returns void, but not sure why.

bdoguc: There is a significant error in my formula:

=IFERROR(INDEX('Rankings - Data'!$A:$A,SMALL(IF('Rankings - Data'!$C$2:$C$21=Rankings!B5,ROW('Rankings - Data'!$C$2:$C$21)),COUNTIF(Rankings!$B$5:B5,Rankings!B5))),"")

Jeffrey... this one of yours is odd!!!! It certainly won't work relaibly if there are ties... The missing value has me scratching my head...

This is odd... raised to the power of extreme oddness.

Replace B7 with int(b7) or ROUND(B7,0) and it works. Remove them and it's an error again. Overtype the formula with 214 and it works.

Increase the number of dps and 214 remains 214. =EXACT(B7,'Rankings - Data'!C4) returns TRUE.

It MUST be floating point arithmetic at work as subtract them like this:

=b7-'Rankings - Data'!C4 and you get 0.000000000000000 However, like this: =(B7-'Rankings - Data'!C4)
you get: 0.000000000000028421709430404

Indeed weird. I did everything you did, and yes, it works, especially when overtyping the number 214.

It's time for some frothy suds over here. I'll let this one ferment for a while and see what occurs to me...