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

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

Comments(45) Filed in category: Excel, Unique distinct values, VLOOKUP and return multiple values

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

Learn the basics of Excel arrays

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

Comments(2) Filed in category: Count values, Excel

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)