Exceljet

Quick, clean, and to the point

Self-contained VLOOKUP

Excel formula: Self-contained VLOOKUP
Generic formula 
=VLOOKUP(lookup,{table_array},column,match)
Summary 

To make a self-contained lookup formula, you can use an array constant inside of the VLOOKUP function. In the example shown, the formula in F7 is:

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

This formula assigns a grade to the score in cell E7 based on the table seen in B6:C10, but the table used for the lookup is hard-coded into the formula.

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

Author 
Dave Bruns

Excel Formula Training

Formulas are the key to getting things done in Excel. In this accelerated training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You'll also learn how to troubleshoot, trace errors, and fix problems. Instant access. See details here.

Download 100+ Important Excel Functions

Get over 100 Excel Functions you should know in one handy PDF.