In a previous post I described how to do lookups in a related table. In this post I am going to show you how to extract unique distinct values and duplicates from a related table.

In these two examples there are two tables and they have a column in common (Company).

Unique distinct values

Array formula in cell B21:

=IFERROR(INDEX(Table2[Sales persons], SMALL(IF((ISERROR(MATCH(Table2[Company], IF($B$19=Table1[Item], Table1[Company], ""), 0)))+COUNTIF($B$20:B20,Table2[Sales persons]), "", MATCH(ROW(Table2[Company]), ROW(Table2[Company]))), 1)), "")

How to create an array formula

How to copy an array formula

Duplicate values

Array formula in cell B21:

=IFERROR(INDEX(Table6[Sales persons], SMALL(IF((NOT(ISERROR(MATCH(Table6[Company], IF($B$19=Table5[Item], Table5[Company], ""), 0)))*COUNTIFS(Table6[Company],Table6[Company],Table6[Sales persons],Table6[Sales persons])>1), MATCH(ROW(Table6[Company]), ROW(Table6[Company])), ""), ROW(A1))), "")

How to create an array formula

  1. Copy above array formula
  2. Select cell B21
  3. Click in formula bar
  4. Paste (Ctrl + v)
  5. Press and hold Ctrl + Shift
  6. Press Enter
  7. Release all keys

The formula in the formula bar is now surrounded by curly brackets: {=array_formula}

How to copy an array formula

  1. Select cell B21
  2. Copy cell (Ctrl + c)
  3. Select cell range B22:B23
  4. Paste (Ctrl + v)

Download excel *.xlsx file

Unique distinct values related tables2.xlsx