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)), "") + CTRL + SHIFT + ENTER copied down to H16.

Earlier Excel versions, array formula in H2:

=IF(ISERROR(INDEX(List, MATCH(0, COUNTIF(H1:$H$1, List)+IF(Category2<>Criteria2, 1, 0)+IF(Category1<>Criteria1, 1, 0), 0)), ""));"";INDEX(List, MATCH(0, COUNTIF(H1:$H$1, List)+IF(Category2<>Criteria2, 1, 0)+IF(Category1<>Criteria1, 1, 0), 0)), "")) + CTRL + SHIFT + ENTER copied down to H16.

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

What is named ranges?

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.

Download excel sample file for this tutorial.
create-a-list-of-distinct-values-from-two-criteria.xlsx
(Excel 2007 Workbook *.xlsx)

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

  • Share/Bookmark

Related posts:

  1. Extract a unique distinct list from three columns in excel
  2. Extract a unique distinct list from two columns using excel 2007 array formula
  3. Create unique distinct list from column where an adjacent column meets criteria
  4. Create unique list from two columns
  5. Create a unique distinct list and sort by occurrances from large to small
  6. Create a unique distinct list from a date range in excel
  7. Count unique distinct values in two columns with date criteria in excel
  8. Unique distinct values from multiple columns using array formula
  9. Extract a unique distinct list sorted from A-Z from range in excel
  10. Unique distinct list sorted based on occurrance in a column in excel