Excel 2010 Bible

Part II: Working with Formulas and Functions

376

The following array formula returns the smallest value in the Change column. This formula returns

–11, which represents Nancy’s test scores.

{=MIN(C2:C15-B2:B15)}

Using an array in lieu of a range reference ...............................................................

If your formula uses a function that requires a range reference, you may be able to replace that

range reference with an array constant. This is useful in situations in which the values in the refer-

enced range do not change.

Note
A notable exception to using an array constant in place of a range reference in a function is with the database
functions that use a reference to a criteria range (for example, DSUM). Unfortunately, using an array constant
instead of a reference to a criteria range does not work. n

Cross-Reference
For information about lookup formulas, see Chapter 14. n

Figure 16.18 shows a worksheet that uses a lookup table to display a word that corresponds to an

integer. For example, looking up a value of 9 returns Nine from the lookup table in D1:E10. The

formula in cell C1 is

=VLOOKUP(B1,D1:E10,2,FALSE)

FIGURE 16.18

You can replace the lookup table in D1:E10 with an array constant.