Article updated on February 06, 2018

I am looking for the same formula on this page, but targeting a range of MxN (spanning multiple columns) not just a list in one column.

I see where you have formulas which act on MxN but not with all the features of this one:

1. Unique and distinct
2. Remove blanks
3. Sort
4. Properly handle numbers and text

And just to ask for the 'frosting on top' remove errors.

The array formula described in this post:

• Filters unique distinct values
• Returns values sorted
• Handles numbers and text together
• Removes blank cells
• Removes errors

Excel 2007/2010 array formula in cell B8:

=IFERROR(SMALL(IF((tbl<>"")*(ISNUMBER(tbl))*(COUNTIF(\$B\$7:B7, tbl)=0), tbl, ""), 1), IFERROR(INDEX(tbl, SMALL(IF(SMALL(IF((COUNTIF(\$B\$7:B7, tbl)=0)*(ISTEXT(tbl)), COUNTIF(tbl, "<"&tbl)+1, ""), 1)=IF((COUNTIF(\$B\$7:B7, tbl)=0)*(ISTEXT(tbl)), COUNTIF(tbl, "<"&tbl)+1, ""), ROW(tbl)-MIN(ROW(tbl))+1), 1), MATCH(SMALL(IF((COUNTIF(\$B\$7:B7, tbl)=0)*(ISTEXT(tbl)), COUNTIF(tbl, "<"&tbl)+1, ""), 1), INDEX(IF((COUNTIF(\$B\$7:B7, tbl)=0)*(ISTEXT(tbl)), COUNTIF(tbl, "<"&tbl)+1, ""), SMALL(IF(SMALL(IF((COUNTIF(\$B\$7:B7, tbl)=0)*(ISTEXT(tbl)), COUNTIF(tbl, "<"&tbl)+1, ""), 1)=IF((COUNTIF(\$B\$7:B7, tbl)=0)*(ISTEXT(tbl)), COUNTIF(tbl, "<"&tbl)+1, ""), ROW(tbl)-MIN(ROW(tbl))+1), 1), , 1), 0), 1), ""))

Named range

tbl : B2:E5

How to create a named range

1. Select cell range B2:E5
2. Type tbl in name box
3. Press Enter

How to create an array formula

1. Copy (Ctrl + c) and paste (Ctrl + v) array formula into formula bar.
2. Press and hold Ctrl + Shift.
3. Press Enter once.
4. Release all keys.

extract-a-unique-distinct-list-sorted-alphabetically-from-a-range removing blanks.xlsx
(Excel 2007 Workbook *.xlsx)

### Excel 2007/2010 array formula: Filter duplicate values, sorted and blanks removed

Array formula in cell B8:

=IFERROR(SMALL(IF((tbl<>"")*(ISNUMBER(tbl))*(COUNTIF(\$B\$7:B7, tbl)=0)*(COUNTIF(tbl, tbl)>1), tbl, ""), 1), IFERROR(INDEX(tbl, SMALL(IF(SMALL(IF((COUNTIF(\$B\$7:B7, tbl)=0)*(ISTEXT(tbl)*(COUNTIF(tbl, tbl)>1)), COUNTIF(tbl, "<"&tbl)+1, ""), 1)=IF((COUNTIF(\$B\$7:B7, tbl)=0)*(ISTEXT(tbl)*(COUNTIF(tbl, tbl)>1)), COUNTIF(tbl, "<"&tbl)+1, ""), ROW(tbl)-MIN(ROW(tbl))+1), 1), MATCH(SMALL(IF((COUNTIF(\$B\$7:B7, tbl)=0)*(ISTEXT(tbl)*(COUNTIF(tbl, tbl)>1)), COUNTIF(tbl, "<"&tbl)+1, ""), 1), INDEX(IF((COUNTIF(\$B\$7:B7, tbl)=0)*(ISTEXT(tbl)*(COUNTIF(tbl, tbl)>1)), COUNTIF(tbl, "<"&tbl)+1, ""), SMALL(IF(SMALL(IF((COUNTIF(\$B\$7:B7, tbl)=0)*(ISTEXT(tbl)*(COUNTIF(tbl, tbl)>1)), COUNTIF(tbl, "<"&tbl)+1, ""), 1)=IF((COUNTIF(\$B\$7:B7, tbl)=0)*(ISTEXT(tbl)*(COUNTIF(tbl, tbl)>1)), COUNTIF(tbl, "<"&tbl)+1, ""), ROW(tbl)-MIN(ROW(tbl))+1), 1), , 1), 0), 1), ""))