You can use the following formula to find unique values in non-adjacent columns in Excel:

=SORT(UNIQUE(FILTER(A1:C11,{1,0,1})))

This particular formula returns the unique combinations of values in the range A1:C11 for columns A and C only.

Note that the values in the curly brackets indicate which columns to include when finding unique values.

The number 1 specifies to include a column while 0 specifies to exclude it.

For example:

• The first 1 indicates that column A should be included.
• The 0 indicates that column B should be excluded.
• The second 1 indicates that column C should be included.

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

Example: Find Unique Values in Non-Adjacent Columns in Excel

Suppose we have the following dataset that contains information about basketball players on various teams:

Suppose we would like to find the unique combinations of teams and positions.

Since the team and position columns are not directly next to each other, we can’t simply use the UNIQUE function.

Instead, we must use the following formula:

=SORT(UNIQUE(FILTER(A1:C11,{1,0,1})))

We can type this formula into cell D2 and then press Enter:

Note that in this example we only used two non-adjacent columns but you can include as many non-adjacent columns as you’d like by using more 1‘s and 0‘s in the formula.

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