The array formula in cell E6 filters values in column C based on value in cell E3, the output is a sorted unique distinct list in cell E6 and below.

Array formula in cell E6:

=INDEX($C$3:$C$11, MATCH(SMALL(IF((COUNTIF($E$5:E5, $C$3:$C$11)=0)*($B$3:$B$11=$E$3), COUNTIF($C$3:$C$11, "<"&$C$3:$C$11), ""), 1), COUNTIF($C$3:$C$11, "<"&$C$3:$C$11), 0))

Recommended post

Sorting numbers and text cells also removing blanks using an array formula

Question: How do I sort text and numbers and also removing blanks using an array formula? Answer: Array formula in […]

Comments(22) Filed in category: Excel

How to create an array formula

  1. Double click on cell E6
  2. Copy (Ctrl + c) and paste (Ctrl + v) array formula to cell
  3. Press and hold Ctrl + Shift simultaneously
  4. Press Enter once.
  5. Release all keys.

There are now a beginning and ending curly bracket in the formula bar, like this: {=formula}
Don't enter these characters yourself.

Explaining array formula in cell E6

Read my explanation here: Create a unique distinct alphabetically sorted list, extracted from a column

Recommended reading:

Extract and sort text cells from a range containing both numerical and text values

Array formula in B16: =INDEX(tbl, MIN(IF(SMALL(IF(ISTEXT(tbl), COUNTIF(tbl, "<"&tbl)), ROW(1:1))=IF(ISTEXT(tbl), COUNTIF(tbl, "<"&tbl)), ROW(tbl)-MIN(ROW(tbl))+1)), MATCH(SMALL(IF(ISTEXT(tbl), COUNTIF(tbl, "<"&tbl)), ROW(1:1)), INDEX(IF(ISTEXT(tbl), COUNTIF(tbl, "<"&tbl)), MIN(IF(SMALL(IF(ISTEXT(tbl), […]

Comments(1) Filed in category: Excel, Sort values

Download excel *.xlsx file

Create a unique distinct alphabetically sorted list with criteria.xlsx