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. Normally, the second argument for VLOOKUP is provided as a range like B6:C10: When the formula is evaluated, the reference B6:C10 is converted internally to two-dimensional array like this: 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: 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.
Dave Bruns
Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.
