Sean asks:

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.

Answer:

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)

Functions in this article:

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

Related posts:

Filter unique distinct row records in excel 2007

Vlookup – Return multiple unique distinct values in excel

Filter unique distinct text values in a range using “contain” condition in excel

Filter unique text values in a range using “contain” condition in excel

Filter unique distinct values using “contain” condition of a column in excel