Author: Oscar Cronquist Article last updated on February 16, 2018

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)