Microsoft Office Tutorials and References

In Depth Information

**Using Single-Cell Array Formulas**

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

referenced range do not change.

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.

Figure 14-17 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 14-17:
You can replace the lookup table in D1:E10 with an array constant.

You can use a two-dimensional array in place of the lookup range. The following formula returns

the same result as the previous formula, but it does not require the lookup range in D1:E1:

=VLOOKUP(B1,{1,”One”;2,”Two”;3,”Three”;4,”Four”;5,”Five”;

6,”Six”;7,”Seven”;8,”Eight”;9,”Nine”;10,”Ten”},2,FALSE)