Hi - Dave here.
I had an interesting question this week about how to assign points based on rank. Specifically, how to rank scores, and then award points based on the rank of each score.
In the worksheet below, I used the RANK function to calculate rank in column D, and INDEX and MATCH to assign points in column E. The formula in E5 is:
=INDEX(tblPoints[Points],MATCH([@Rank],tblPoints[Rank],1))
[
download the workbook and read the full explanation]
In the example above, everybody gets at least 3 points, no matter what their rank. In the article, I explain how to modify the formula to assign no points above a rank of 6. The worksheet also provides XLOOKUP and VLOOKUP alternatives, and links to learn more about Excel Tables.
Excel formulas
We maintain a large list of working formulas
here.
If you need more structure, we also offer
video training.
Have a fun and safe weekend!
Dave
