In a previous article "Automatically filter unique row records from multiple columns", i presented a solution to filter out unique values from several columns. In this article i want to show you how to narrow that search down a bit further. This time i want to search for unique values with a singel column criteria that must match.

I want to search for unique distinct values with column B only containing value "A"

Array formula in G2:

=INDEX($A$2:$D$25, MATCH(0, IF($B$2:$B$25="A", COUNTIF($G$1:$G1, $A$2:$A$25)*COUNTIF($H$1:$H1, $B$2:$B$25)*COUNTIF($I$1:$I1, $C$2:$C$25)*COUNTIF($J$1:$J1, $D$2:$D$25)), 0), COLUMN(A:A)) + CTRL + SHIFT + ENTER copied to the right to J2 and then copied down as far as needed.

See picture below.

Download excel sample file for this tutorial.
Extract unique distinct records.xls
(Excel 97-2003 Workbook *.xls)

Functions in this tutorial

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

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

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

COLUMN(reference)
Returns the column number of a reference

Share and Enjoy:
  • Share/Bookmark

Related posts:

  1. Create unique distinct list sorted based on text length using array formula in excel
  2. Filter unique distinct list sorted based on sum of adjacent values using array formula in excel
  3. Unique distinct list sorted based on occurrance in a column in excel
  4. Lookup with multiple criteria and display multiple search results using excel formula, part 3
  5. Lookup with multiple criteria and display multiple search results using excel formula, part 2
  6. Vlookup with 2 or more lookup criteria and return multiple matches in excel
  7. Lookup with multiple criteria and display multiple search results using excel formula, part 4
  8. Lookup with multiple criteria and display multiple search results using excel formula
  9. Unique distinct values from multiple columns using array formula
  10. Filter unique distinct text values in a range using “contain” condition in excel