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.



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))

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 copy array formula

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


Download excel example file.
(Excel 97-2003 Workbook *.xls)