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