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

Answer:

distinct-unique-list-where-adjacents-columns-meet-two-criteria

Excel 2007 array formula in H2:

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

Recommended reading

Create unique distinct list from column where an adjacent column meets criteria

Question: I want a unique list to be created from a column where an adjacent column has cell values between […]

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.

Learn the basics of Excel arrays

Array formulas allows you to do advanced calculations not possible with regular formulas.

Copy cell H2. Paste down to cell H16.

Earlier Excel versions, array formula in H2:

=IF(ISERROR(INDEX(List, MATCH(0, COUNTIF(J1:$J$1, List)+IF(Category2<>Criteria2, 1, 0)+IF(Category1<>Criteria1, 1, 0), 0))),"",INDEX(List, MATCH(0, COUNTIF(J1:$J$1, List)+IF(Category2<>Criteria2, 1, 0)+IF(Category1<>Criteria1, 1, 0), 0))) + CTRL + SHIFT + ENTER

Copy cell H2. Paste down to cell H16.

Recommended reading:

Unique distinct list to be created from a column where an adjacent column has text cell values

Question: I want a unique list to be created from a column where an adjacent column has text cell value? […]

Named ranges

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

What is named ranges?

Recommended reading:

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 […]

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

Learn to use the COUNTIF function

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

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

IF function explained

Check if a logical expression is met. Returns a specific value if TRUE and another specific value if FALSE.

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

How to use Excel’s MATCH function

Identify the position of a value in an array.

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

INDEX function explained

Fetch a value in a data set based on coordinates.

Step 6 - Check if expression returns an error

=IFERROR(4, "")

returns number 4 in cell H2.

IFERROR function

The IFERROR function was introduced in excel 2007. In previous excel versions you could check for errors with the ISERROR […]

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)