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

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, \$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.

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.

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.

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.

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.