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

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 […]

#### 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}