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: See picture below.

Formula displaying item numbers (H2:H16):

=IF(ROW()-1>SUM(IF($F$1=$B$2:$B$16, IF(MATCH($B$2:$B$16&$C$2:$C$16, $B$2:$B$16&$C$2:$C$16, 0)=ROW($B$2:$B$16)-1, 1, ""), "")), "", SMALL(IF($F$1=$B$2:$B$16, IF(MATCH($B$2:$B$16&$C$2:$C$16, $B$2:$B$16&$C$2:$C$16, 0)=ROW()-1, ROW()-1, ""), ""), ROW()-1)) + CTRL + SHIFT + ENTER

Formula displaying categories (I2:I16):

=IF(ROW()-1>SUM(IF($F$1=$B$2:$B$16, IF(MATCH($B$2:$B$16&$C$2:$C$16, $B$2:$B$16&$C$2:$C$16, 0)=ROW($B$2:$B$16)-1, 1, ""), "")), "", INDEX($B$2:$B$16, $H$2:$H$16)) + CTRL + SHIFT + ENTER

Formula displaying values (J2:J16):

=IF(ROW()-1>SUM(IF($F$1=$B$2:$B$16, IF(MATCH($B$2:$B$16&$C$2:$C$16, $B$2:$B$16&$C$2:$C$16, 0)=ROW($B$2:$B$16)-1, 1, ""), "")), "", INDEX($C$2:$C$16, $H$2:$H$16))

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

Functions in this article:

IF(logical_test;[value_if:true];[value_if_false])
Checks whether a condition is met, and returns one value if TRUE, and another value if FALSE

COUNT(value1;[value2])
Counts the number of cells in a range that contain numbers

ROW(reference) returns the rownumber of a reference

MATCH(lookup_value;lookup_array; [match_type])
Returns the relative position of an item in an array that matches a specified value

SUM(number1,[number2],)
Adds all the numbers in a range of cells

INDEX(array,row_num,[column_num])
Returns a value or reference of the cell at the intersection of a particular row and column, in a given range

Related posts:

1. Highlight duplicates where adjacent cell value meets criteria using conditional formatting in excel part 2
2. Filter unique distinct list sorted based on sum of adjacent values using array formula in excel
3. Sum values in a range where adjacent cell value equals a criterion in excel
4. Create a unique distinct text list from a range containing both numerical and text values in excel
5. Filter unique distinct values where adjacent cells contain search string in excel
6. Create unique distinct list sorted based on text length using array formula in excel