If Tea and Coffee has Americano,it will only return Americano once and not twice. I am looking for a unique distinct list based on each condition. I remember reading that Excel has difficulty with these type of or conditions in arrays.

### Array formula in cell C19:

=INDEX(\$C\$5:\$D\$11, MATCH(0, COUNTIFS(\$C\$18:C18, Category, \$D\$18:D18, Item)+(COUNTIF(\$C\$13:\$C\$14, Category)=0), 0), COLUMN(A1))

### How to create an array formula

1. Create named ranges.
2. Copy (Ctrl + c) and paste (Ctrl + v) array formula into formula bar. See above picture.
3. Press and hold Ctrl + Shift.
4. Press Enter once.
5. Release all keys.

### Copy array formula

1. Select cell C19
2. Copy cell (Ctrl + c)
3. Select cell range C19:D22
4. Paste (Ctrl + v)

### Named ranges

Category C5:C11
Item D5:D11

1. Select cell range C5:C11
2. Type category in name box. See above picture.
3. Repeat above steps with Item

### How to remove errors

IFERROR(value;value_if_error) returns value_if_error if expression is an error and the value of the expression itself otherwise

The array formula becomes:

=IFERROR(INDEX(\$C\$5:\$D\$11, MATCH(0, COUNTIFS(\$C\$18:C18, Category, \$D\$18:D18, Item)+(COUNTIF(\$C\$13:\$C\$14, Category)=0), 0), COLUMN(A1)), "")

### How the array formula in cell C19 works

Step 1 - Identify unique distinct records

INDEX(\$C\$5:\$D\$11, MATCH(0, COUNTIFS(\$C\$18:C18, Category, \$D\$18:D18, Item)+(COUNTIF(\$C\$13:\$C\$14, Category)=0), 0), COLUMN(A1))

COUNTIFS(\$C\$18:C18, Category, \$D\$18:D18, Item)

becomes

COUNTIFS("Category", {"Coffee";"Coffee";"Coffee";"Coffee";"tea";"juice";"tea"}, "Item", {"Espresso";"Espresso";"Americano";"Americano";"Americano";"Florida";"English"},)

and returns {0;0;0;0;0;0;0}

Step 2 - Filter records with condition

INDEX(\$C\$5:\$D\$11, MATCH(0, COUNTIFS(\$C\$18:C18, Category, \$D\$18:D18, Item)+(COUNTIF(\$C\$13:\$C\$14, Category)=0), 0), COLUMN(A1))

COUNTIF(\$C\$13:\$C\$14, Category)=0

becomes

COUNTIF({"Coffee"; "tea"}, {"Coffee"; "Coffee"; "Coffee"; "Coffee"; "tea"; "juice"; "tea"})=0

and returns {FALSE; FALSE; FALSE; FALSE; FALSE; TRUE; FALSE}

Step 3 - Match filtered records

INDEX(\$C\$5:\$D\$11, MATCH(0, COUNTIFS(\$C\$18:C18, Category, \$D\$18:D18, Item)+(COUNTIF(\$C\$13:\$C\$14, Category)=0), 0), COLUMN(A1))

MATCH(0, COUNTIFS(\$C\$18:C18, Category, \$D\$18:D18, Item)+(COUNTIF(\$C\$13:\$C\$14, Category)=0), 0)

becomes

MATCH(0, {0;0;0;0;0;0;0}+{FALSE; FALSE; FALSE; FALSE; FALSE; TRUE; FALSE}, 0)

becomes

MATCH(0, {0;0;0;0;0;1;0},0)

and returns 1.

Step 4 - Return a value or reference of the cell at the intersection of a particular row and column

INDEX(\$C\$5:\$D\$11, MATCH(0, COUNTIFS(\$C\$18:C18, Category, \$D\$18:D18, Item)+(COUNTIF(\$C\$13:\$C\$14, Category)=0), 0), COLUMN(A1))

becomes

INDEX({"Coffee", "Espresso";"Coffee", "Espresso";"Coffee", "Americano";"Coffee", "Americano";"tea", "Americano";"juice", "Florida";"tea", "English"}, 1, 1)

and returns "Coffee" in cell C19.

### Download excel sample file for this tutorial.

Filter unique distinct records with a condition.xlsx
(Excel 2007/2010 Workbook *.xlsx)

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

IFERROR(value;value_if_error)
Returns value_if_error if expression is an error and the value of the expression itself otherwise

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

COUNTIFS(criteria_range1,criteria1, criteria_range2, criteria2...)
Counts the number of cells specified by a given set of conditions or criteria

COUNTIF(range,criteria)
Counts the number of cells within a range that meet the given condition