This blog post describes how to extract common rows (records) from two lists in excel 2007.

Sheet: List 1

Sheet: List 2

Comparing the two lists

We need to know what columns to compare before we can create the array formula.

In this example:
List 1:Column A - List 2: Column E
List 1:Column B - List 2: Column B
List 1:Column C - List 2: Column D

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

COUNTIFS('List 2'!$E$2:$E$13,  'List 1'!$A$2:$A$11, 'List 2'!$B$2:$B$13, 'List 1'!$B$2:$B$11, 'List 2'!$D$2:$D$13, 'List 1'!$C$2:$C$11)

With the countifs function complete we can now construct the array formula.

Array formula

Sheet: Common records

Array formula in cell A2:

=INDEX('List 1'!$A$2:$C$11, SMALL(IF(COUNTIFS('List 2'!$E$2:$E$13,  'List 1'!$A$2:$A$11, 'List 2'!$B$2:$B$13, 'List 1'!$B$2:$B$11, 'List 2'!$D$2:$D$13, 'List 1'!$C$2:$C$11)>0, ROW('List 1'!$A$2:$A$11)-MIN(ROW('List 1'!$A$2:$A$11))+1), ROW(A1)), COLUMN(A1)) + CTRL + SHIFT + ENTER.

Copy cell A2 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 A2

Step 1 - Find common records

=INDEX('List 1'!$A$2:$C$11, SMALL(IF(COUNTIFS('List 2'!$E$2:$E$13,  'List 1'!$A$2:$A$11, 'List 2'!$B$2:$B$13, 'List 1'!$B$2:$B$11, 'List 2'!$D$2:$D$13, 'List 1'!$C$2:$C$11)>0, ROW('List 1'!$A$2:$A$11)-MIN(ROW('List 1'!$A$2:$A$11))+1), ROW(A1)), COLUMN(A1))

COUNTIFS('List 2'!$E$2:$E$13,  'List 1'!$A$2:$A$11, 'List 2'!$B$2:$B$13, 'List 1'!$B$2:$B$11, 'List 2'!$D$2:$D$13, 'List 1'!$C$2:$C$11)

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}

Step 2 - Identify row numbers

=INDEX('List 1'!$A$2:$C$11, SMALL(IF(COUNTIFS('List 2'!$E$2:$E$13,  'List 1'!$A$2:$A$11, 'List 2'!$B$2:$B$13, 'List 1'!$B$2:$B$11, 'List 2'!$D$2:$D$13, 'List 1'!$C$2:$C$11)>0, ROW('List 1'!$A$2:$A$11)-MIN(ROW('List 1'!$A$2:$A$11))+1), ROW(A1)), COLUMN(A1))

IF(COUNTIFS('List 2'!$E$2:$E$13,  'List 1'!$A$2:$A$11, 'List 2'!$B$2:$B$13, 'List 1'!$B$2:$B$11, 'List 2'!$D$2:$D$13, 'List 1'!$C$2:$C$11)>0, ROW('List 1'!$A$2:$A$11)-MIN(ROW('List 1'!$A$2:$A$11))+1)

becomes

IF({1, 1, 1, 0, 1, 1, 1, 1, 0, 0}>0, ROW('List 1'!$A$2:$A$11)-MIN(ROW('List 1'!$A$2:$A$11))+1)

becomes

IF({TRUE, TRUE, TRUE, FALSE, TRUE, TRUE, TRUE, TRUE, FALSE, FALSE}, ROW('List 1'!$A$2:$A$11)-MIN(ROW('List 1'!$A$2:$A$11))+1)

becomes

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

and returns {1,2,3,FALSE,5,6,7,FALSE,FALSE}

Step 3 - Return the k-th smallest value

SMALL(IF(COUNTIFS('List 2'!$E$2:$E$13,  'List 1'!$A$2:$A$11, 'List 2'!$B$2:$B$13, 'List 1'!$B$2:$B$11, 'List 2'!$D$2:$D$13, 'List 1'!$C$2:$C$11)>0, ROW('List 1'!$A$2:$A$11)-MIN(ROW('List 1'!$A$2:$A$11))+1), ROW(A1))

becomes

SMALL({1,2,3,FALSE,5,6,7,FALSE,FALSE}, ROW(A1))

becomes

SMALL({1,2,3,FALSE,5,6,7,FALSE,FALSE}, 1)

and returns 1.

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('List 2'!$E$2:$E$13,  'List 1'!$A$2:$A$11, 'List 2'!$B$2:$B$13, 'List 1'!$B$2:$B$11, 'List 2'!$D$2:$D$13, 'List 1'!$C$2:$C$11)>0, ROW('List 1'!$A$2:$A$11)-MIN(ROW('List 1'!$A$2:$A$11))+1), ROW(A1)), COLUMN(A1))

becomes

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

becomes

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

and returns 1997.

Download excel sample file for this tutorial.

Compare lists.xlsx
(Excel 2007 Workbook *.xlsx)

Functions used in this formula:

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