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

Weekly Blog EMAIL

Welcome! I am Oscar and here to help you out.

Feel free to comment and ask Excel questions.

Make sure you subscribe to my newsletter so you don't miss new blog articles.

Related articles