Array Formulas - Examples

Linking Cells

Lets suppose that you have a list of column headings above a table and that you want to repeat the column headings on various rows on your worksheet.

The first column headings in row 2 are just entered as text (as you would normally).
The cells you want linked, in this case a set of column headings is entered as an array formula.
Highlight the cells "B5:E5" and enter the formula "=B2:E2" in the formula bar pressing (Ctrl + Shift + Enter).
You can quickly create an array constant from a linked array by editing the array block and pressing F9.

Removing Intermediate Formulas

Using array formulas can remove the need for intermediate formulas.
Formula - Sum cells that are numbers after multipying
Formula - Average cells after subtraction

Comparing Two Tables

Lets suppose you have two tables of information and you want to compare the values to see how many match.

The formula in cell "E13" loops through all the cells in the range and compares each element using the IF function.
The results from this are then passed as an array to the SUM function.
Both the ranges must contain the same number of elements, otherwise an error will be returned.

Removing Blanks

There are a number of different formulas you can use to achieve this.
You don't have to use an Array Formula, there are regular formulas that can do this.
Cells & Ranges > Removing Blank Cells Horizontal
Cells & Ranges > Removing Blank Cells Vertical

Example 1 - Vertical

=IF(COUNTIF($B$2:$B$12,"?*")<ROW(B2)-1,"",INDEX(B:B,SMALL(IF(B$2:B$12<>"", 
ROW(B$2:B$12)),ROWS(B$2:B2)))) 

can be used with other data in the column
can be dragged down
doesn't need to be on the same rows
only works on vertical lists

Example 2 - Vertical

Thank you to Chip Pearson (cpearson.com/excel/NoBlanks.aspx)
Enter the formulas as an array formula into the first cell and then drag the formula down.

=IF(ROW()-ROW($B$2:$B$12)+1>ROWS($B$2:$B$12)-COUNTBLANK($B$2:$B$12),"", 
INDIRECT(ADDRESS(SMALL((IF($B$2:$B$12<>"",ROW($B$2:$B$12),ROW()+ROWS($B$2:$B$12))), 
ROW()-ROW($D$2:$D$12)+1),COLUMN($B$2:$B$12),4))) 

can be dragged down
doesn't have to be on the same rows
only works on vertical lists