In this example we are going to use two lists with identical columns in excel 2007. It is easy to modify the countifs function if your columns are not ordered.

This is the first example list on sheet: List 1

Sheet: List 2

Create named ranges

There is really no need for named ranges in this example, although they shorten array formulas considerably.

1. Select A2:A13 on sheet "List 2"
2. Type Year in Name Box
3. Press Enter

Repeat with remaining ranges:
Sheet: List 2 , Range:B2:B13, Name: Asset
Sheet: List 2 , Range:C2:C13, Name: Cost
Sheet: List 1 , Range:A2:A11, Name: Year1
Sheet: List 1 , Range:B2:B11, Name: Asset1
Sheet: List 1 , Range:C2:C11, Name: Cost1

Filter records existing in only one list, excel 2007

Array formula in cell A4:

Copy cell A4 and paste it to the right as far as needed. Copy cells and paste down as far as needed.

Array formula in cell A14:

Copy cell A4 and paste it to the right as far as needed. Copy cells and paste down as far as needed.

Explaining the array formula in cell A4

Step 1 - Find records existing only in List 1

Let us take a look at the bolded part of the formula.

=INDEX('List 1'!$A$2:$C$11, SMALL(IF(COUNTIFS(Year,  Year1,  Asset,  Asset1,  Cost,  Cost1)=0, ROW(Year1)-MIN(ROW(Year1))+1), ROW(A1)), COLUMN(A1))

COUNTIFS(Year,  Year1,  Asset,  Asset1,  Cost,  Cost1)

becomes

COUNTIFS({1997, 1998, 1998, 1999, 2001, 2002, 2002, 2002, 2002, 2003, 2003, 2004},{1997, 1998, 1998, 1999, 2001, 2002, 2002, 2002, 1996, 2002}, {Printer, Press, Copier, Copier, Printer, Copier, Printer, Computer, Lift, Vacuum, Press, Copier},{Printer, Press, Copier, Copier, Printer, Copier, Printer, Computer, Computer, Copier},{2025, 11000, 1575, 1199, 825, 1231, 1788, 17090, 15464, 359, 6900, 799},{2025, 11000, 1575, 1299, 825, 1231, 1788, 17090, 15275, 1577})

and returns this array: {1, 1, 1, 0, 1, 1, 1, 1, 0, 0}

COUNTIFS(Year,  Year1,  Asset,  Asset1,  Cost,  Cost1)=0

becomes

{1, 1, 1, 0, 1, 1, 1, 1, 0, 0}=0

and returns

{FALSE, FALSE, FALSE, TRUE, FALSE, FALSE, FALSE, FALSE, TRUE, TRUE}

Step 2 - Convert boolean array to row numbers

IF(COUNTIFS(Year,  Year1,  Asset,  Asset1,  Cost,  Cost1)=0, ROW(Year1)-MIN(ROW(Year1))+1)

becomes

IF({FALSE, FALSE, FALSE, TRUE, FALSE, FALSE, FALSE, FALSE, TRUE, TRUE}, ROW(Year1)-MIN(ROW(Year1))+1)

becomes

IF({FALSE, FALSE, FALSE, TRUE, FALSE, FALSE, FALSE, FALSE, TRUE, TRUE}, {1, 2, 3, 4, 5, 6, 7, 8, 9, 10})

and returns

{FALSE, FALSE, FALSE, 4, FALSE, FALSE, FALSE, FALSE, 9, 10}

Step 3 - Return the k-th smallest value

SMALL(IF(COUNTIFS(Year,  Year1,  Asset,  Asset1,  Cost,  Cost1)=0, ROW(Year1)-MIN(ROW(Year1))+1), ROW(A1))

becomes

SMALL({FALSE, FALSE, FALSE, 4, FALSE, FALSE, FALSE, FALSE, 9, 10}, ROW(A1))

becomes

SMALL({FALSE, FALSE, FALSE, 4, FALSE, FALSE, FALSE, FALSE, 9, 10}, 1)

and returns 4.

Step 4 - Return a value of the cell at the intersection of a particular row and column

=INDEX('List 1'!$A$2:$C$11, SMALL(IF(COUNTIFS(Year,  Year1,  Asset,  Asset1,  Cost,  Cost1)=0, ROW(Year1)-MIN(ROW(Year1))+1), ROW(A1)), COLUMN(A1))

becomes

=INDEX('List 1'!$A$2:$C$11, 4, COLUMN(A1))

becomes

=INDEX('List 1'!$A$2:$C$11, 4, 1)

and returns 1999.

Download excel sample file for this tutorial.

Filter records existing in only one list.xlsx
(Excel 2007 Workbook *.xlsx)

Functions used in article:

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

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

COUNTIFS(criteria_range1,criteria1, criteria_range2, criteria2...)
Counts the number of cells specified by a given set of conditions or criteria

SMALL(array,k) returns the k-th smallest row number in this data set.

ROW(reference) returns the rownumber of a reference

MIN(number1,[number2])
Returns the smallest number in a set of values. Ignores logical values and text

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