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.

Explanation

This worksheet demonstrates a clever way to look up prices that change based on a selected tier. Imagine a pricing system where the cost of a product depends on both the product color and a tier (e.g., “Bronze,” “Silver,” or “Gold”). The challenge is to pull the correct price based on both inputs. At the core of this solution is a standard INDEX and MATCH formula. However, since the prices are organized in blocks corresponding to each tier, we need a way to “step” through the prices correctly based on the selected tier. This is a case where the underlying numeric behavior of INDEX and MATCH formulas lends itself well to simple “step” adjustments based on a clear pattern in the lookup data.

This is a specific technique for doing lookups in a table that is well-structured but not in a way that would make it easy to use a “normal” multiple criteria lookup formula. You can use this approach when the data has a regular pattern that can be navigated numerically. You can see a real-world application of this approach on this page , which explains how to calculate US income tax in brackets based on tax rates published yearly by the IRS. The “step” approach is used to fetch tax rates dynamically based on the selected taxpayer status and year.

Worksheet setup

Step-based lookup example - worksheet layout - 1
  • The worksheet contains pricing for four colors (Red, Blue, Green, and White) in three tiers (Bronze, Silver, and Gold). This means there are 12 prices in total.
  • All pricing is stored in columns B and C. The pricing is organized in blocks corresponding to each tier. The order of these blocks follows the order of the tier list in column E.
  • Cell E5 contains a dropdown menu for selecting a tier, created with data validation that pulls values from E8:E10. When a user selects a tier in E5, the prices in column H should update to reflect the selected tier.
  • For convenience and readability, prices (C5:C16), colors (B5:B16), selection (E5), and tiers (E8:E10) are named ranges .
  • The formula in cell H5, copied down, is:
=INDEX(prices,MATCH(G5,colors,0)+(MATCH(selection,tiers,0)-1)*4)

Core INDEX and MATCH formula

The core of this solution is a standard INDEX and MATCH like this:

=INDEX(prices,MATCH(G5,colors,0))

This formula is designed to look up the price for a given color. Inside the INDEX function, the array is given as the named range prices (C5:C16). The row_num is calculated with the MATCH function, which does the work of figuring out the correct row to get a price from:

MATCH(G5,colors,0)

MATCH is configured as follows:

  • lookup_value - G5 (“Red”)
  • lookup_array - colors (B5:B16)
  • match_type - 0 (to require an exact match)

Since G5 = “Red”, MATCH finds “Red” at row 1 in colors (B5:B16) and returns 1. INDEX then returns 30.00 as a final result:

=INDEX(prices,1) // returns 30.00

In the next row, G6 contains “blue”, so MATCH finds “Red” at row 2, and INDEX returns 28.00:

=INDEX(prices,2) // returns 28.00

The same process repeats for “Green” and “White” in the next two rows. This formula works great. However, it has a key limitation: because of how MATCH works, it always retrieves a price for the first match of a color listed in column B. This means the formula only works for Tier 1 pricing (Bronze). To correctly retrieve prices for Silver or Gold (Tier 2 or Tier 3), we need a way to adjust the row number dynamically. This is where the idea of the numeric step adjustment comes into play.

Numeric step adjustment

The second part of the formula is designed to shift the row index as needed to determine the correct price for the selected tier (Bronze, Silver, or Gold):

(MATCH(selection,tiers,0)-1)*4

This is the “numeric step adjustment” part of the formula. Let’s break it down logically. This MATCH function locates the selected tier (from E5) in the tiers list (E8:E10) and returns a numeric index. The 0 ensures an exact match. The output from MATCH looks like this:

  • If E5 = “Bronze”, MATCH returns 1.
  • If E5 = “Silver”, MATCH returns 2.
  • If E5 = “Gold”, MATCH returns 3.

The next step is to subtract 1. The logic for this works as follows: Since Bronze is the first tier (starting point), we don’t need to adjust anything when Bronze is selected. However, we do need to adjust the results for Silver and Gold:

  • Bronze (Tier 1) → 1 - 1 = 0 → No row shift needed.
  • Silver (Tier 2) → 2 - 1 = 1 → We need to shift down one full block.
  • Gold (Tier 3) → 3 - 1 = 2 → We need to shift down two full blocks.

The final step is multiplying by 4 to ensure we jump down the correct number of rows.

  • Bronze (1st tier): 0 * 4 = 0 → No shift.
  • Silver (2nd tier): 1 * 4 = 4 → Move 4 rows down.
  • Gold (3rd tier): 2 * 4 = 8 → Move 8 rows down.

By adding this calculated step adjustment, the formula correctly shifts the lookup row to match the selected tier. For example, if G5 = “Red” and E5 = “Silver” the step adjustment works like this:

  • MATCH(G5, colors, 0)) returns 1.
  • MATCH(selection, tiers, 0) returns 2
  • (2 - 1) * 4 = 4, so we move 4 rows down.
  • INDEX(prices, 1 + 4) = INDEX(prices, 5)
  • The final result is 27.00

To recap, the step adjustment works like this:

  • MATCH(selection, tiers, 0) → Finds which tier is selected.
  • Subtracting 1 converts to a zero-based index (so Bronze = 0).
  • Multiplying by 4 moves the lookup down in full-tier blocks (each containing 4 rows).
  • The result is dynamic, so the correct price is pulled for the selected tier.

Conclusion

A step-based lookup formula is a clever way to retrieve data when a table follows a consistent pattern but lacks the information needed to apply multiple criteria. By using a numeric adjustment, this approach allows you to dynamically “jump” to the correct value within a structured table. This method works best when the data follows a regular pattern that can be navigated with predictable steps calculated based on user input. A step-based lookup formula is a simple but powerful way to efficiently look up information without restructuring the data.