Author: Oscar Cronquist Article last updated on January 18, 2019

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

To enter an array formula, type the formula in a cell then press and hold CTRL + SHIFT simultaneously, now press Enter once. Release all keys.

The formula bar now shows the formula with a beginning and ending curly bracket telling you that you entered the formula successfully. Don't enter the curly brackets yourself.

Copy array formula

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

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}

How to use the COUNTIFS function

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

How to use the COUNTIFS function

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}

How to use the COUNTIF function

Counts the number of cells that meet a specific condition.

How to use the COUNTIF function

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.

How to use the MATCH function

Identify the position of a value in an array.

How to use the MATCH function

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.

How to use the INDEX function

Gets a value in a specific cell range based on a row and column number.

How to use the 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.

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

Download Excel file


Filter-unique-distinct-records-with-a-condition.xlsx

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)), "")

Related articles