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}

Recommended articles

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.

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}

Recommended articles

How to use the COUNTIF function
Counts the number of cells that meet a specific condition.

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.

Recommended articles

How to use the MATCH function
Identify the position of a value in an array.

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.

Recommended articles

How to use the INDEX function
Gets a value in a specific cell range based on a row and column number.

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

Recommended articles

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

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