Question: How do I create a unique distinct list where other columns meet two criteria using excel array formula?

Answer:

Excel 2007 array formula in H2:

How to create an array formula

1. Copy (Ctrl + c) and paste (Ctrl + v) array formula into formula bar.
2. Press and hold Ctrl + Shift.
3. Press Enter once.
4. Release all keys.

Copy cell H2. Paste down to cell H16.

Earlier Excel versions, array formula in H2:

Copy cell H2. Paste down to cell H16.

Named ranges

List (C2:C16)
Category1 (A2:A16)
Category2 (B2:B16)
Criteria1 (F1)
Criteria2 (F2)

What is named ranges?

How to create named ranges

1. Select cell range C2:C16
2. Type List in name box

How to implement array formula to your workbook

Change named ranges. If your unique distinct list starts at, for example, F3. Change H1:$H$1 in the above formula to F2:$F$2. The ranges don´t have to be adjacent.

Explaining excel 2007 array formula in cell H2

Step 1 - Create boolean array with values indicating unique distinct values

=IFERROR(INDEX(List, MATCH(0, COUNTIF(H1:$H$1, List)+IF(Category2<>Criteria2, 1, 0)+IF(Category1<>Criteria1, 1, 0), 0)), "")

COUNTIF(H1:$H$1, List)

becomes

COUNTIF("Distinct unique list", {4; 2; 1; 2; 2; 4; 2; 3; 1; 1; 3; 4; 2; 5; 7})

and returns {0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0}.

Step 2 - Create boolean array with values indicating first criterion

=IFERROR(INDEX(List, MATCH(0, COUNTIF(H1:$H$1, List)+IF(Category2<>Criteria2, 1, 0)+IF(Category1<>Criteria1, 1, 0), 0)), "")

IF(Category1<>Criteria1, 1, 0)

becomes

IF({"BB"; "AA"; "AA"; "BB"; "BB"; "BB"; "BB"; "BB"; "AA"; "BB"; "BB"; "BB"; "AA"; "BB"; "BB"}<>"BB", 1, 0)

and returns {0;1;1;0;0;0;0;0;1;0;0;0;1;0;0}.

Step 3 - Create boolean array with values indicating second criterion

=IFERROR(INDEX(List, MATCH(0, COUNTIF(H1:$H$1, List)+IF(Category2<>Criteria2, 1, 0)+IF(Category1<>Criteria1, 1, 0), 0)), "")

IF(Category2<>Criteria2, 1, 0)

becomes

IF({"A"; "B"; "A"; "B"; "A"; "B"; "A"; "B"; "A"; "B"; "A"; "B"; "A"; "B"; "C"}<>"A", 1, 0)

and returns {0; 1; 0; 1; 0; 1; 0; 1; 0; 1; 0; 1; 0; 1; 1}.

Step 4 - Add boolean arrays

=IFERROR(INDEX(List, MATCH(0, COUNTIF(H1:$H$1, List)+IF(Category2<>Criteria2, 1, 0)+IF(Category1<>Criteria1, 1, 0), 0)), "")

becomes

=IFERROR(INDEX(List, MATCH(0,{0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0}+{0; 1; 0; 1; 0; 1; 0; 1; 0; 1; 0; 1; 0; 1; 1}+{0;1;1;0;0;0;0;0;1;0;0;0;1;0;0}, 0)), "")

becomes

=IFERROR(INDEX(List, MATCH(0,{0;2;1;1;0;1;0;1;1;1;0;1;1;1;1}, 0)), "")

Step 4 - Return relative position of the first zero (0) in array

=IFERROR(INDEX(List, MATCH(0,{0;2;1;1;0;1;0;1;1;1;0;1;1;1;1}, 0)), "")

becomes

=IFERROR(INDEX(List, 1), "")

Step 5 - Return a value or reference of the cell at the intersection of a particular row and column

=IFERROR(INDEX(List, 1), "")

becomes

=IFERROR(4, "")

Step 6 - Check if expression returns an error

=IFERROR(4, "")

returns number 4 in cell H2.

Download excel file.

create-a-list-of-distinct-values-from-two-criteria.xlsx
(Excel 2007 Workbook *.xlsx)

create-a-list-of-distinct-values-from-two-criteria.xls
(Excel 97-2003 Workbook *.xls)

Functions in this article:

MATCH(lookup_value;lookup_array; [match_type]
Returns the relative position of an item in an array that matches a specified value

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

IF(logical_test;[value_if:true];[value_if_false])
Checks whether a condition is met, and returns one value if TRUE, and another value if FALSE

COUNTIF(range,criteria)
Counts the number of cells within a range that meet the given condition

This blog article is one out of thirteen articles on the same subject "unique".

• How to extract a unique distinct list from a column in excel
  
• Extract a unique distinct list from two columns using excel 2007 array formula
  
• Extract a unique distinct list from three columns in excel
• Extract distinct unique sorted year and month list from a date series in excel
  
• Create a unique distinct list from a date range in excel
  
• Unique values from multiple columns using array formulas
  
• Extract a unique distinct list sorted from A-Z from range in excel
  
• Sort a range by occurence using array formula in excel
  
• Filter unique distinct values from two ranges combined in excel 2007
• Create a unique list and sort by occurrances from large to small
• Unique list to be created from a column where an adjacent column has text cell values
• Create unique list from column where an adjacent column meets criteria
• How to create a unique distinct list where other columns meet two criteria