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

  • 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

Explanation

Introduction

A key limitation of VLOOKUP is it can only look up values to the right . In other words, the column with lookup values must be to the left of the values you want to retrieve with VLOOKUP. As a result, with standard configuration, there is no way to use VLOOKUP to “look left” and reverse the original lookup. From the standpoint of VLOOKUP, we can visualize the problem like this:

The table we have versus the table we need - 1

The workaround explained below uses the CHOOSE function to rearrange the table inside VLOOKUP. Starting at the beginning, the formula in H5 is a normal VLOOKUP formula:

=VLOOKUP(G5,B5:D8,3,0) // returns 3000

Using G5 as the lookup value (“C”), and the data in B5:D8 as the table array , VLOOKUP performs a lookup on values in column B and returns the corresponding value from column 3 (column D), 3000. Notice zero (0) is provided as the last argument to force an exact match.

The formula in G10 simply pulls the result from H5:

=H5 // 3000

To perform a reverse lookup, the formula in H10 is:

=VLOOKUP(G10,CHOOSE({3,2,1},B5:B8,C5:C8,D5:D8),3,0)

The tricky bit is the CHOOSE function, which is used to rearrange the table array so that Cost is the first column, and Option is the last:

CHOOSE({3,2,1},B5:B8,C5:C8,D5:D8) // reorder table 3, 2, 1

The CHOOSE function is designed to select a value based on a numeric index. In this case, we are supplying three index values in an array constant :

{3,2,1} // array constant

In other words, we are asking for column 3, then column 2, then column 1. This is followed by the three ranges that represent each column of the table in the order they appear on the worksheet.

With this configuration, CHOOSE returns all three columns in a single 2D array like this:

{1000,"Silver","A";2000,"Gold","B";3000,"Platinum","C";5000,"Diamond","D"}

If we visualize this array as a table on the worksheet, we have:

Table rearranged by CHOOSE function - 2

Note: the headings are not part of the array and are shown here for clarity only.

Effectively, we have swapped columns 1 and 3. The reorganized table is returned directly to VLOOKUP, which matches 3000, and returns the corresponding value from column 3, “C”.

With INDEX and MATCH

The above solution works fine, but it is hard to recommend since most users will not understand how the formula works. A better solution is INDEX and MATCH , using a formula like this:

=INDEX(B5:B8,MATCH(G10,D5:D8,0))

Here, the MATCH function finds the value 3000 in D5:D8, and returns its position, 3:

MATCH(G10,D5:D8,0) // returns 3

Note: MATCH is configured for an exact match by setting the last argument to zero (0).

MATCH returns a result directly to INDEX as the row number, so the formula becomes:

=INDEX(B5:B8,3) // returns "C"

and INDEX returns the value from the third row of B5:B8, “C”.

This formula shows how INDEX and MATCH can be more flexible than VLOOKUP.

With XLOOKUP

XLOOKUP also provides a very good solution. The equivalent formula is:

=XLOOKUP(G10,D5:D8,B5:B8) // returns "C"

With a lookup_value from G10 (3000), a l ookup_array of D5:D8 (costs), and a results_array of B5:B8 (options), XLOOKUP locates the 3000 in the lookup array and returns the corresponding item from the results array, “C”. Because XLOOKUP performs an exact match by default, there is no need to set the match mode explicitly.