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

The array formula in column G filters values in column C using a condition in cell E3, comparing it with values in adjacent column B. The filtered values are then sorted from A to Z.

It is possible to build a formula around the VLOOKUP function but it would be big, the following formula is smaller and easier to understand.

Array formula in cell G3:

=INDEX($C$3:$C$10, MATCH(SMALL(IF($E$3=$B$3:$B$10, COUNTIF($C$3:$C$10, "<"&$C$3:$C$10), ""),ROWS($A$1:A1)), COUNTIF($C$3:$C$10,"<"&$C$3:$C$10), 0))

Watch a video where I explain the formula

https://www.youtube.com/watch?v=er3oED4Hr5w

Recommended article

Unique distinct list sorted alphabetically based on a condition

The array formula in cell E6 filters values in column C based on value in cell E3, the output is […]

Unique distinct list sorted alphabetically based on a condition

How to enter an array formula

  1. Double click on cell G3
  2. Copy and paste above formula to cell G3
  3. Press and hold CTRL + SHIFT simultaneously
  4. Press Enter once
    1. Release all keys

Examine the formula bar and you will see that the formula now has a beginning and ending curly bracket. Don't enter these characters yourself, they appear automatically. Example, {=array_formula}