Author: Oscar Cronquist Article last updated on August 28, 2017

Question: How do I create a distinct list from column C (Values) where cell values in column B (Category) equals the value AA? See picture below.

Answer:

Array formula in cell H2:

=INDEX(\$C\$2:\$C\$16, MATCH(0, IF(\$F\$1=\$B\$2:\$B\$16, COUNTIF(\$H\$1:\$H1, \$C\$2:\$C\$16), ""), 0))

If you don't want to use an array formula, read this post:

Vlookup – Return multiple unique distinct values

Ahmed Ali asks: How to return multiple values using vlookup in excel and removing duplicates? I have tried the formula […]

How to create an array formula

1. Copy above array formula
2. Select cell H2
3. Click in formula bar
4. Paste (Ctrl + v)
5. Press and hold Ctrl + Shift
6. Press Enter

How to enter an array formula

Array formulas allows you to do advanced calculations not possible with regular formulas.

How to copy array formula

1. Select cell H2
2. Copy cell (Ctrl +c)
3. Select cell H3
4. Paste (Ctrl + v)

Download excel example file.
create-a-list-of-distinct-values-where-adjacent-cell-value-meets-criteria11.xls
(Excel 97-2003 Workbook *.xls)