Author: Oscar Cronquist Article last updated on June 11, 2018

The picture above shows an array formula in cell B9:C13 that extracts unique distinct records from two tables in cell range B3:C6 and E3:F6.

If a record exists in both tables only one record is returned by the formula. If a record exists multiple times in one table only one record is returned by the formula.

Example, John 42 exists in both tables, however, the formula returns only one instance of John 42.

Laura 26 exists multiple times but only in the first table, the formula returns only one record of Laura 26.

Array formula in cell B9:

=IFNA(INDEX($B$3:$C$6, MATCH(0, COUNTIFS($B$8:B8, $B$3:$B$6, $C$8:C8, $C$3:$C$6), 0),COLUMNS($A$1:A1)), INDEX($E$3:$F$6,MATCH(0, COUNTIFS($B$8:B8, $E$3:$E$6, $C$8:C8, $F$3:$F$6), 0), COLUMNS($A$1:A1)))

To enter an array formula, type the formula in cell B9 then press and hold CTRL + SHIFT simultaneously, now press Enter once. Release all keys.

The formula bar now shows the formula with a beginning and ending curly bracket telling you that you entered the formula successfully.

Don't enter the curly brackets yourself, they appear automatically.

What is a unique distinct record?

Unique distinct records are all records except duplicates merged into one distinct value.