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.

Explanation

The goal in this example is to create a self-contained lookup formula to assign a grade to the score in cell E7, based on the table in B6:C10. However, instead of providing B6:B10 as a reference for the table_array argument, the table is provided as a constant .

Normally, the second argument for VLOOKUP is provided as a range like B6:C10:

=VLOOKUP(E7,B6:C10,2,TRUE)

When the formula is evaluated, the reference B6:C10 is converted internally to a two-dimensional array like this:

{0,"F";60,"D";70,"C";80,"B";90,"A"}

Each comma indicates a column, and each semi-colon indicates a row. Knowing this, when a table is small, you can convert the table to an " array constant " and use the constant inside VLOOKUP, instead of the reference. In the example shown, the formula used is:

=VLOOKUP(E7,{0,"F";60,"D";70,"C";80,"B";90,"A"},2,TRUE)

This formula is functionally the same as the “standard” form above. However, the advantage is that you no longer need to maintain a table on the worksheet. The disadvantage is that the array is hard-coded into the formula. If you copy the formula to more than one cell, you will have to maintain more than one instance of the array constant. Editing an array constant is more difficult than editing a table on the worksheet, and other users may not understand the formula. Nonetheless, there are situations where a self-contained lookup formula is handy.

Creating an array constant

If the array constant is simple like {“a”,“b”,“c”} it is easy to type manually. However, two-dimensional tables have a more complex syntax. An easy to create an array constant it to flow these steps:

  1. In an empty cell, type “=”.
  2. Select the range you want to covert (don’t include the headers).
  3. In the formula bar, type F9 to “evaluate” the range.
  4. Excel will convert the range reference into an array constant.
  5. Copy the array constant (don’t include the =) and paste it into your formula.

Named range option

Another way to avoid placing a table on the worksheet is to create a named range using the array constant as the value, then refer to the named range in VLOOKUP for the table array. This way, the table doesn’t need to appear on a worksheet, and there is only one instance of the table in the workbook to maintain.

Creating the array constant

To create an array constant for a range, start by entering the table normally on the worksheet. Then, in an empty cell, start a formula with the equal sign (=) and select the range that contains the table. Then use the shortcut F9 to convert the reference to an array constant, and copy to the clipboard. You can then paste the table into a formula as a constant.