Excel: How to Find Unique Values from Multiple Columns

by Tutor Aspire January 17, 2023

You can use the following formula to find unique values from multiple columns in Excel:

=INDIRECT(TEXT(MIN(IF(($A$2:$C$11"")*(COUNTIF($E$1:E1,$A$2:$C$11)=0),ROW($2:$11)*100+COLUMN($A:$C),7^8)),"R0C00"),)&""

This particular formula finds the unique values in the cell range A2:C11 and returns a list of the unique values in column E.

The following example shows how to use this formula in practice.

Suppose we have the following three lists of basketball team names in Excel:

To find the unique values from each of the three columns, we can use the following formula:

=INDIRECT(TEXT(MIN(IF(($A$2:$C$11"")*(COUNTIF($E$1:E1,$A$2:$C$11)=0),ROW($2:$11)*100+COLUMN($A:$C),7^8)),"R0C00"),)&""

We can type this formula into cell E2 and press Ctrl+Shift+Enter:

We can then click and drag this formula down to other cells in column E until blank values start appearing:

From the output we can see that there are 11 unique team names across the three columns of team names.

Note: To use this formula with more columns, simply modify the A2:C11 cell range in the formula to include more columns.

The following tutorials explain how to perform other common operations in Excel: