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, $C$5:$C$11, $D$18:D18, $D$5:$D$11)+(COUNTIF($C$13:$C$14, $C$5:$C$11)=0), 0), COLUMN(A1))

Recommended article

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

Recommended article

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

Copy array formula

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

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, $C$5:$C$11, $D$18:D18, $D$5:$D$11)+(COUNTIF($C$13:$C$14, $C$5:$C$11)=0), 0), COLUMN(A1)), "")

How the array formula in cell C19 works

Step 1 - Identify unique distinct records

COUNTIFS($C$18:C18, $C$5:$C$11, $D$18:D18, $D$5:$D$11)

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}

COUNTIFS function

Checks multiple conditions against the same number of cell ranges and counts how many times all criteria are met.

Comments(1) Filed in category: Excel

Step 2 - Filter records with condition

COUNTIF($C$13:$C$14, $C$5:$C$11)=0

becomes

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

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

COUNTIF function

Counts the number of times a value exists in a cell range.

Comments(5) Filed in category: Excel

Step 3 - Match filtered records

MATCH(0, COUNTIFS($C$18:C18, $C$5:$C$11, $D$18:D18, $D$5:$D$11)+(COUNTIF($C$13:$C$14, $C$5:$C$11)=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.

MATCH function

Identify the position of a value in an array.

Comments(12) Filed in category: Excel

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, $C$5:$C$11, $D$18:D18, $D$5:$D$11)+(COUNTIF($C$13:$C$14, $C$5:$C$11)=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.

INDEX function explained

Fetch a value in a data set based on coordinates.

Comments(14) Filed in category: Cross reference table, Excel, INDEX function

Tip! Did you know that a pivot table can easily extract unique distinct records too?

Discover Pivot Tables – Excel’s most powerful feature and also least known

A pivot table allows you to examine data more efficiently, it can summarize large amounts of data very quickly and is very easy to use.

Comments(3) Filed in category: Excel, Pivot table

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