Exceljet

Quick, clean, and to the point

Rank and score with INDEX and MATCH

Excel formula: Rank and score with INDEX and MATCH
Summary 

To rank and assign points based on a score, you can use INDEX and MATCH with a table that maps rank to points. In the example shown, the formula in E5, copied down, is:

=INDEX(tblPoints[Points],MATCH([@Rank],tblPoints[Rank],1))

where tblData (B5:E15) and tblPoints (G5:H10) are Excel Tables.

Explanation 

In this example, the goal is to assign points based on a ranked score. The scores to rank are in column C, and the calculated Rank is in column D. Points are awarded based on the table in G5:H10. Both tblData (B5:E15) and tblPoints (G5:H10) are Excel Tables created with Control + T.

Background study

Calculating rank

The first part of the problem is to calculate the rank of each score in tblData. This is done with the RANK function like this:

=RANK([@Score],[Score])

This is an example of a structured reference, a special kind of reference that makes formulas that deal with Excel Tables easier to work with. Essentially, we get the rank of the current score [@Score], against all the other scores in [Score].

Calculating points

Now that we have the rank in column D, we have what we need to calculate points. This is done by looking up the correct number of points to assign with INDEX and MATCH in the tblPoints table. The formula in column E is:

=INDEX(tblPoints[Points],MATCH([@Rank],tblPoints[Rank],1))

Working from the inside out, we first figure out what row in the tblPoints table applies to the rank we are looking up with the MATCH function:

MATCH([@Rank],tblPoints[Rank],1)

The lookup_value is the Rank in column D, and the lookup_array is tblPoints[Rank]. Match_type is provided as 1, because we want to match the largest value in tblPoints[Rank] that is less than or equal to the Rank in column D. This doesn't affect the first 6 ranks, which will match exactly. But notice that once we reach rank 6, points drop to 3 and this will apply to every rank after 6. Because Katrina's rank is 3, MATCH returns 3, which corresponds to row 3 in tblPoints.

MATCH returns this position directly to the INDEX function as the row_num argument. The Points column of tblPoints is provided for array:

=INDEX(tblPoints[Points],3) // returns 10

INDEX returns 10 as the points awarded to Katrina. The points for the remaining rows in the table are calculated in the same way. See below for an adjustment to assign zero points to ranks after 6.

No points after rank 6

In the example shown, we assign at least 3 points to every rank using MATCH in approximate match mode. If you want to assign zero points after a rank of 6, one option is to adjust the formula so that the MATCH function performs an exact match, then wrap the entire formula in the IFNA function like this:

=IFNA(INDEX(tblPoints[Points],MATCH([@Rank],tblPoints[Rank],0)),0)

Setting match_type to 0 causes MATCH to perform an exact match. This will cause MATCH to return the #N/A error for any rank over 6. The IFNA function "catches" this error when it occurs, and returns 0. We could also use the IFERROR function to do the same thing, but IFERROR will catch any error, so IFNA is a bit more conservative.

Note: another easy option is to use the original formula, but add another row to the points table with rank = 7 and points = 0.

With XLOOKUP or VLOOKUP

You can easily use the XLOOKUP function or the VLOOKUP function to lookup points, as an alternative to INDEX and MATCH. With the XLOOKUP function, the formula looks like this:

=XLOOKUP(tblData[@Rank],tblPoints[Rank],tblPoints[Points],,-1)

Note the match_mode argument works a bit differently than match_type in the MATCH function. For exact match or next smaller, we need to use -1.

With the VLOOKUP function, the formula looks like this:

=VLOOKUP(tblData[@Rank],tblPoints,2,TRUE)

The last argument, range_lookup, tells VLOOKUP to use approximate matching. In this mode, VLOOKUP will return an exact match or next smallest value. Range_lookup is optional and defaults to TRUE, but I like to set a value as a reminder of what is expected. 

Training

If you want to learn more about Excel Tables or Excel Formulas, we offer online video training.

Author 
Dave Bruns

Excel Formula Training

Formulas are the key to getting things done in Excel. In this accelerated training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You'll also learn how to troubleshoot, trace errors, and fix problems. Instant access. See details here.

Download 100+ Important Excel Functions

Get over 100 Excel Functions you should know in one handy PDF.