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




Custom Search