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. Create a list of duplicates where adjacent cell value meets a condition in excel
2. Create unique distinct list from column where an adjacent column meets criteria
3. Create and sort distinct list by adjacent cell value size
4. Highlight duplicates where adjacent cell value meets criteria using conditional formatting in excel
5. Highlight duplicates where adjacent cell value meets criteria using conditional formatting in excel part 2
6. Unique list to be created from a column where an adjacent column has text cell values
7. How to create a unique distinct list where other columns meet two criteria
8. Filter unique distinct list sorted based on sum of adjacent values using array formula in excel
9. Create a drop down list containing only unique distinct alphabetically sorted text values using excel array formula
10. Sum values in a range where adjacent cell value equals a criterion in excel