Microsoft Office Tutorials and References

In Depth Information

**Working with Multicell Array Formulas**

Figure 15-11:
A multicell array formula displays the values in column A, sorted.

This formula doesn’t work if the
Data
range contains any blank cells. The unfilled cells of the array

formula display #NUM!.

The following modified version eliminates the #NUM! display by using the IFERROR function,

introduced in Excel 2007:

{=IFERROR(INDEX(Data,SMALL(IF(MATCH(Data,Data,0)=ROW(INDIRECT

(“1:”&ROWS(data))),MATCH(Data,Data,0),””),ROW(INDIRECT

(“1:”&ROWS(Data))))),””)}

Figure 15-12 shows an example. Range A4:A22 is named
Data
, and the array formula is entered

into range C4:C22. Range E4:E23 contains the array formula that uses the IFERROR function.