By John Walkenbach
 Adapted for Office Online by Colin Wilcox
 

Now that you've learned how to create array formulas and array constants in Excel, here are some examples of how to use them.

Applies to
Microsoft Office Excel 2003 Excel 2002

---

See all Power User columns
See all columns

---

This is a reference page of examples for use with the Power User columns Introducing Array Formulas in Excel and More Arrays: Introducing Array Constants in Excel. To make the best use of the examples provided here, you need to be familiar with the content of those columns. If you prefer the depth and detail that a book can provide, see chapters 14 and 15 of Excel 2002 Formulas, by John Walkenbach.

The examples in this reference show you some basic ways to put array formulas to work in Microsoft Excel. For more advanced examples, see Putting Advanced Array Formulas to Work. For a larger set of both basic and advanced examples, see John's book.

Get started

Use the data in this section to create two sample worksheets.

1. Open an existing workbook with two blank worksheets, or create a new workbook.
2. Copy the data in the following table, and paste it into cells A1 through A10 of the first blank worksheet. Use the Paste Options button to match the destination formatting.
   
   

   400
   1200
   3200
   475
   500
   2000
   600
   1700
   800
   2700

3. Copy the data in the following table, and paste it into cells C1 through C5 of the first blank worksheet. Use the Paste Options button to match the destination formatting.
   
   

   the quick

   brown fox

   jumped over

   the lazy

   power user

4. Copy the data in the following table, and paste it into cells E1 through H4. Again, match the destination formatting.
   
   

   1	2	3	4
   5	6	7	8
   9	10	11	12
   13	14	15	16

   Your finished worksheet should look like this.

   

5. Name the first worksheet Data, and then name a second blank worksheet Arrays.

Create arrays and array constants from existing values

The following example explains how to use array formulas to create links between ranges of cells in different worksheets. It also shows you how to create an array constant from the same set of values.

To create an array from existing values

1. In your sample workbook, go to the Arrays worksheet.
2. Select the cell range C1 through E3.
3. Enter the following formula in the Formula Bar, and then press CTRL+SHIFT+ENTER:

   =Data!E1:G3

You see the following result.

The formula links to some of the values stored in cells E1 through G3 on the Data worksheet. The alternative, of course, is to place a unique formula in each cell of the Arrays worksheet, like so.

=Data!E1	=Data!F1	=Data!G1
=Data!E2	=Data!F2	=Data!G2
=Data!E3	=Data!F3	=Data!G3

If you change some of the values on the Data worksheet, those changes appear on the Arrays worksheet. Remember that to change any values on the Data worksheet, you have to follow the rules for editing array formulas. For more information about those rules, see the first column in this series.

To create an array constant from existing values

1. On the Arrays worksheet, select cells C1 through E3.
2. Press F2 to start edit mode.
3. Press F9 to convert the cell references to values. Excel converts the values into an array constant.
4. Press CTRL+SHIFT+ENTER to enter the array constant as an array formula.

Excel replaces the =Data!E1:G3 array formula with the following array constant: ={1,2,3;5,6,7;9,10,11}. What you've done is sever the link between the Data and Arrays worksheets.

Count characters in a range of cells

The following example shows you how to count the number of characters, including spaces, in a range of cells.

• On the Data worksheet, enter the following formula in cell C7, and then press CTRL+SHIFT+ENTER:

=SUM(LEN(C1:C5))

The value 47 appears in cell C7.

In this case, the LEN function returns the length of each text string in each of the cells in the range. The SUM function adds those values together and writes them to the cell that contains the formula.

Find the n smallest values in a range

This example explains how to find the three smallest values in a range of cells.

1. On the Data worksheet, select cells A12 through A14. This set of cells will hold the results returned by the array formula.
2. In the Formula Bar, enter this formula, and then press CTRL+SHIFT+ENTER:

=SMALL(A1:A10,{1;2;3})

The values 400, 475, and 500 appear cells A12 through A14, respectively.

This formula uses an array constant to evaluate the SMALL function three times and return the smallest (1), second smallest (2), and third smallest (3) members in the A1:A10 array. If you want to find more values, you add more arguments to the constant and an equivalent number of result cells to the A12:A14 range. You can also run additional functions with this formula, such as summing or averaging the values:

=SUM(SMALL(A1:A10,{1;2;3}))

=AVERAGE(SMALL(A1:A10,{1;2;3}))

Find the n largest values in a range

To find the largest values in a range, you can replace the SMALL argument that we discussed earlier with LARGE. This example puts a twist on that formula — the ROW and INDIRECT functions. We'll discuss those after you run the formula.

1. On the Data worksheet, select cells A12 through A14.
2. Press DELETE to clear the existing formula but leave the cells selected.
3. In the Formula Bar, enter this formula, and then press CTRL+SHIFT+ENTER:

=LARGE(A1:A10,ROW(INDIRECT("1:3")))

The values 3200, 2700, and 2000 appear in cells A12 through A14, respectively.

At this point, it may help to know a bit about the ROW and INDIRECT functions. You can use the ROW function to create an array of consecutive integers. For example, select an empty column of 10 cells in your practice workbook, and then enter this array formula:

=ROW(1:10)

The formula creates a column of 10 consecutive integers. That's a nice time saver, but it has a drawback. To see the problem, insert a row above the range that contains the array formula. Excel adjusts the row references, and the formula generates integers from 2 to 11. To fix that problem, you add the INDIRECT function to the formula:

=ROW(INDIRECT("1:10"))

The INDIRECT function uses text strings as its arguments (that's why the range 1:10 is surrounded by double quotes). Excel does not adjust text values when you insert rows or otherwise move the array formula. As a result, the ROW function always generates the array of integers that you want.

So, starting from the inner parentheses and working outward: The INDIRECT function returns a set of text values, in this case the values 1 through 3. The ROW function in turn generates a columnar array consisting of one through three. The LARGE function uses the values in the cell range A1:A10, and it is evaluated three times, once for each reference returned by the ROW function. If you want to find more values, you add a greater cell range to the INDIRECT argument.

Finally, you can use this formula with other functions, such as SUM and AVERAGE.

Find the longest text string in a range of cells

This example finds the longest string of text in a range of cells. This formula works only when a data range contains a single column of cells.

• On the Data worksheet, clear the existing formula from cell C7, enter the following formula in that cell, and then press CTRL+SHIFT+ENTER: =INDEX(C1:C5,MATCH(MAX(LEN(C1:C5)),LEN(C1:C5),0),1)

The value jumped over appears in cell C7.

Let's examine the formula, starting from the inner elements and working outward. The LEN function returns the length of the items in the cell range C1:C5. The MAX function calculates the largest value, which corresponds to the longest text string.

Here's where things get a little complex. The MATCH function calculates the offset (the relative position) of the cell that contains the longest text string. To do that, it requires three arguments, a lookup value, a lookup array, and a match type. The MATCH function searches the lookup array for the specified lookup value. In this case, the lookup value is the longest text string:

(MAX(LEN(C1:C5))

and that string resides in this array:

LEN(C1:C5)

The match type argument is 0. The match type can consist of a 1, 0, or -1. If you specify 1, MATCH returns the largest value that is less than or equal to the lookup value. If you specify 0, MATCH returns the first value exactly equal to the lookup value. If you specify -1, MATCH finds the smallest value that is greater than or equal to the specified lookup value. If you omit a match type, Excel assumes a 1.

Finally, the INDEX function takes several arguments: an array, and a row and column number within that array. The cell range C1:C5 provides the array, the MATCH function provides the cell address, and the final argument (1) specifies that the value comes from the first column in the array.

For more information about the functions discussed here, see Help in Excel.

More information

• For an introduction to array formulas, see Introducing Array Formulas in Excel and More Arrays: Introducing Array Constants in Excel.
• For examples of some advanced array formulas, see Putting Advanced Array Formulas to Work.

---

About the authors

• John Walkenbach is an Excel Most Valuable Professional (MVP) and author of Excel 2002 Formulas, the book that provides the information for this set of columns. To learn more about John's other books, see his book page.
• Colin Wilcox writes for the Office Help team. In addition to contributing to the Office Power User Corner column, Colin writes articles and tutorials for Microsoft Data Analyzer.

---

See all Power User columns
See all columns