Excel: How to Find Unique Values from Multiple Columns


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.

Example: Find Unique Values from Multiple Columns in Excel

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:

Excel find unique values from multiple columns

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.

Additional Resources

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

How to Count Number of Occurrences in Excel
How to Count Frequency of Text in Excel
How to Calculate Relative Frequency in Excel

Leave a Reply

Your email address will not be published.