Article updated on November 27, 2010

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