Self-contained VLOOKUP

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 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.

Named range option

If you want a self-contained table, but don't want multiple instances of the table in the worksheet, you can create a named range using the array constant, then refer to the the named range in VLOOKUP. The table doesn't need to appear on a worksheet, and there is only one instance of the table 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 a 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.