Excel Function to Generate list of unique entries from multiple columns

Hi and welcome to the forum,

You can use this array formula to extract unique values from Column A

Copy and paste formula in cell F2

Press F2 (located on top left of your keyboard) then press and hold Ctrl and Shift keys together and hit Enter. Drag formula down.


=IFERROR(INDEX($A$2:$A$100,MATCH(0,IF(ISBLANK($A$2:$A$100),"",COUNTIF(F$1:$F1,$A$2:$A$100)),0)),IFERROR(INDEX($B$2:$B$100,MATCH(0,IF(ISBLANK($B$2:$B$100),"",COUNTIF(F$1:$F1,$B$2:$B$100)),0)),IFERROR(INDEX($C$2:$C$100,MATCH(0,IF(ISBLANK($C$2:$C$100),"",COUNTIF(F$1:$F1,$C$2:$C$100)),0)),IFERROR(INDEX($D$2:$D$100,MATCH(0,IF(ISBLANK($D$2:$D$100),"",COUNTIF(F$1:$F1,$D$2:$D$100)),0)),""))))

**Array formulas must be entered with Ctrl+Shift+Enter key combination.

Corrected formula

Listing the unique values from multiple columns means we have an index on each column. When it errors out, it then lists the next unique values of the following column.

Put this in F2:

=IFERROR(IFERROR(IFERROR(IFERROR(INDEX($A$2:$A$21, MATCH(0, COUNTIF($F$1:F1, $A$2:$A$21), 0)), INDEX($B$2:$B$21, MATCH(0, COUNTIF($F$1:F1, $B$2:$B$21), 0))),INDEX($C$2:$C$21, MATCH(0, COUNTIF($F$1:F1, $C$2:$C$21), 0))),INDEX($D$2:$D$21, MATCH(0, COUNTIF($F$1:F1, $D$2:$D$21), 0))), "")

Array formula Confirmed with Ctrl+Shift+Enter

and then copy downwards