Rank and score with INDEX and MATCH

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

• Excel Tables (overview)
• What is an Excel Table (3 min. video)
• Introduction to structured references (3 min. video)
• How to use INDEX and MATCH (overview)
• Basic index and match approximate (example)

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.