How to Filter Column Based on Another Column in Excel (5 Methods)

In this article, we’re going to show you 5 methods of how to use Excel to Filter a column based on another column. To demonstrate these methods, we’ve taken a dataset with 2 columns: “Name” and “Department”. Moreover, We’ll Filter based on the value of the “Department” column.

How to Filter Column Based on Another Column in Excel: 5 Ways

Method 1 – Using Advanced Filter to Filter Column Based on Another Column

Steps:

From the Data tab, select Advanced. The Advanced Filter dialog box will appear.

Set the following cell ranges: C4:C10 as the List range, and E4:E6 as the Criteria range.
Click on OK.

The Name column is Filtered based on another column.

Method 2 – Filter a Column by Applying Excel COUNTIF Function

Steps:

Select the cell range D5:D10.
Copy the following formula.

=COUNTIF($E$5:$E$6,C5)=0

The COUNTIF formula is checking if the value from column C matches the value from column E. If the value is found, then 1 will be the output. Then, we’ll check if this value is 0. If yes, then we’ll get TRUE. Our Filtered column will continue the value FALSE.

Press Ctrl + Enter.

Here, we can see the matched values are showing FALSE.

Select the cell range B4:D10.
From the Data tab, select Filter.

Click on the Filter icon of column D.

Put a tick mark on FALSE.
Press OK.

Thus, we’ve completed yet another method of Filtering columns based on another column.

Method 3 – Combining IF, ISNA, VLOOKUP Functions in Excel to Filter Columns Based on Another Column

Steps:

Enter the following formula in cell D5:

=IF(ISNA(VLOOKUP(C5,$E$5:$E$6,1,FALSE)),"",1)

Formula Breakdown

VLOOKUP(C5,$E$5:$E$6,1,FALSE)
- Output: “Accounting”.
- The VLOOKUP function returns a value from an array or range. We’re looking for the value of “Accounting” in our array (E5:E6). There is only 1 column, hence we’ve put 1. Moreover, we’ve put FALSE for the exact match.
Then our formula reduces to, IF(ISNA(“Accounting”),””,1)
- Output: 1.
- The ISNA function checks if a cell contains the “#N/A” error. If there is that error, then we’ll get TRUE as the output. Lastly, our IF function will work. If there is any error then we’ll get a blank cell, else we’ll get 1. As we found the value in our array, hence we’ve got the value 1 here.

Press Enter and AutoFill the formula.

We’ve received the value 1, as explained above.

We can see there are 3 TRUE values.

Following steps shown in method 2, filter the values containing 1 only.

Method 4 – Incorporating IF, ISNA, MATCH Functions in Excel to Filter Column Based on Another Column

Steps:

Copy the following formula in cell D5:

=IF(ISNA(MATCH(C5,$E$5:$E$6,0)),"",1)

Formula Breakdown

MATCH(C5,$E$5:$E$6,0)
- Output: 1.
- The MATCH function shows the position of a value in an array. Our lookup value is in cell C5. Our lookup array is in E5:E6, and we’re looking for the exact match, hence we put the 0.
Then, our formula reduces to IF(ISNA(1),””,1)
- Output: 1.
- The ISNA function checks if a cell contains the “#N/A” error. If there is that error, then we’ll get TRUE as the output. Lastly, our IF function will work. If there is any error then we’ll get a blank cell, else we’ll get 1. As we found the value in our array, hence we’ve got the value 1 here.

Press Enter and AutoFill the formula.

We’ve got 1 as per the explanation above.

Follow the steps shown in method 2 to filter the values containing 1 only.

Method 5 – Filter Column Based on Another Column by Using FILTER Function in Excel

Available in Excel 2021 onward and Excel 365.

Steps:

Copy the following formula in cell B13:

=FILTER(B4:C10,(C4:C10=E5)+(C4:C10=E6),"")

Formula Breakdown

Our array is B4:C10. We have two criteria that are connected with plus (+). That means if any of the criteria are fulfilled then we’ll get output.
(C4:C10=E5)+(C4:C10=E6)
- Output: {0;1;1;0;0;1;0}.
- We’re checking if the cell range contains our value from cells E5 and E6. Then, we got 3 values that meet our condition.
We’re not defining any arguments in this formula.