How to give an item a value based on it's position in the list

[owenmac99]

Hi,

I have a large dataset of golfers and some of their stats. The list contains 450 players. Each stat is weighted differently, giving the highest ranked player the most points down to the last place getting the least amount of points. Right now this is the code that I have

=IF(B2=MAX(B$2:B$450),Q$10,IF(B2=LARGE(B$2:B$450,2),Q$11,IF(B2=LARGE(B$2:B$450,3),Q$12,IF(B2=LARGE(B$2:B$450,4),Q$13,0))))

The values under column Q being the amount of points that player gets based on a table I made.

So essentially I'm testing the cell to see where it ranks among other cells in that category. This works the way I want, except that I would have to write IF(B2=LARGE(B$2:B$450,2) 450 times. I imagine there is a better way but I don't know how. Any suggestions are greatly appreciated. Thanks!

I included a sample of the sheet the show what I'm trying to do. Hopefully that helps make it clear.

[hrlngrv]

If the values to be sorted were {60;60;60;61;62;65;65;67} with lowest best, MIN(values) = SMALL(values,2) = SMALL(values,3). If N (> 1) different golfers all had the same best value, shouldn't all N of them receive the same points for that value?

My point: use a table. If columns Y and Z were available, and since you show you're using Office 365 so should have spilled formulas,

Y1:

Please Login or Register  to view this content.

Y2:

Please Login or Register  to view this content.

Fill Y2 down until the formula returns 0. Enter the corresponding points in column Z. Use LOOKUP to pull points for each player, e.g.,

Please Login or Register  to view this content.

The -s are needed because column Y would be in descending order. Taking negatives puts them in ascending order, which is what LOOKUP expects.

Point: tables are BY FAR the best way to approach this sort of thing.

OK, you could use XLOOKUP.

Please Login or Register  to view this content.

but from my perspective using negatives for LOOKUP's 1st and 2nd arguments is easier. Then again, if brevity were paramount and all columns in the table were numeric,

Please Login or Register  to view this content.

ADDED: if some other score should be sorted smallest to largest, say, column C, then using columns AB and AC for another table,

AB1:

Please Login or Register  to view this content.

AB2:

Please Login or Register  to view this content.

Fill AB2 down until the formula returns 0, and clear the cell with the formula returning 0. Enter corresponding points in column AC. The LOOKUP formula would be simpler because column AB would be in ascending order.

Please Login or Register  to view this content.

[bebo021999]

Start in F2, we need to determine B2 rank:
(A)=RANK(B2,$B$2:$B$436)
or
(B)=SUMPRODUCT(--(B$2:B$436>=B2))

Then using INDEX() :

Please Login or Register  to view this content.

The only difference between (A) and (B) is which outcome if there were duplicate points.

[kvsrinivasamurthy]

ARRAY formula in F2 then copied across to columns G, H and I

=IFERROR(INDEX(Q$10:Q$22,MATCH(B2,LARGE(B$2:B$450,ROW($1:$13)),0)),"")
.
To enter ARRAY formula
Copy and paste the formula in cell
Press F2
Press Ctrl+Shift+Enter together
Excel covers the formula with {}.

[hrlngrv]

OP's profile shows Office 365. If that's the case, array formula entry no longer needed. Indeed, an unnecessary complication.