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)
 
Search JabSto ::




Custom Search