Author: Oscar Cronquist Article last updated on February 11, 2018

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($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 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:

Download excel *.xlsx file

Create a unique distinct alphabetically sorted list with criteria.xlsx