Excel 2010 Bible

Chapter 17: Performing Magic with Array Formulas

397

The following modified version eliminates the #NUM! display by using the Excel 2007 IFERROR

function.

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

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

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

FIGURE 17.11

A multicell array formula displays the values in column A, sorted.

Figure 17.12 shows an example. Range A4:A22 s named Data, and the array formula is entered

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