Author: Oscar Cronquist Article last updated on December 17, 2020

Update 17 December 2020, the new FILTER, UNIQUE, and SORT functions are now available for Excel 365 users.

=SORT(UNIQUE(FILTER(C3:C11, E3=B3:B11)))

This is entered as a regular formula, however, it returns an array of values and extends automatically to cells below and to the right. Microsoft calls this a dynamic array and spilled array.

The array formula below is for earlier Excel versions, it filters values in column C based on the 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($G$2:G2, $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

How to create an array formula

  1. Double press with left mouse button 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:

Get excel *.xlsx file

Create a unique distinct alphabetically sorted list with criteria.xlsx