Rafiul Haq
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.
Download Practice Workbook
5 Ways to Filter Column Based on Another Column in Excel
1. Using Advanced Filter in Excel to Filter Column Based on Another Column
For the first method, we’ll use the Advanced Filter feature of Excel to Filter a column based on another column.
Steps:
- Firstly, from the Data tab >>> select Advanced.
The Advanced Filter dialog box will appear.
- Secondly, set the following cell range-
- C4:C10 as the List range.
- E4:E6 as the Criteria range.
- Finally, click on OK.
Thus, the Name column is Filtered based on another column.
Read More: Excel VBA to Filter in Same Column by Multiple Criteria (6 Examples)
2. Based on Another Column Filter a Column by Applying Excel COUNTIF Function
In this method, we’re going to use the COUNTIF function to Filter a column based on another column.
Steps:
- Firstly, select the cell range D5:D10.
- Secondly, type the following formula.
=COUNTIF($E$5:$E$6,C5)=0The 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.
- Thirdly, press CTRL + ENTER.
Here, we can see the matched values are showing FALSE.
- Firstly, select the cell range B4:D10.
- Secondly, from the Data tab >>> select Filter.
This time, we’ll notice the Filter icons.
- Thirdly, click on the Filter icon of column D.
- After that, put a tick mark on FALSE.
- Finally, press OK.
Thus, we’ve completed yet another method of Filtering columns based on another column.
Read More: Filter Different Column by Multiple Criteria in Excel VBA
3. Combining IF, ISNA, VLOOKUP Functions in Excel to Filter Column Based on Another Column
In this method, we’ll combine the IF, ISNA, and VLOOKUP functions to create a formula to Filter columns based on another column in Excel.
Steps:
- Firstly, type 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.
- Secondly, press ENTER and AutoFill the formula.
We’ve gotten the value 1, as explained above.
We can see there are 3 TRUE values.
- After that, as shown in method 2, Filter the values containing 1 only.
In conclusion, we’ve shown you a combination formula to Filter columns based on another column.
Related Content: Filter Multiple Criteria in Excel (4 Suitable Ways)
Similar Readings
- Excel VBA: How to Filter with Multiple Criteria in Array (7 Ways)
- How to Use Filter in Protected Excel Sheet (With Easy Steps)
- How to Remove Filter by Color in Excel (5 Methods)
- Excel VBA: Filter Table Based on Cell Value (6 Easy Methods)
- How to Filter Multiple Columns by Color in Excel (2 Methods)
4. Incorporating IF, ISNA, MATCH Functions in Excel to Filter Column Based on Another Column
For the fourth method, we will use the MATCH function along with the IF, and ISNA functions to Filter a column based on another column.
Steps:
- Firstly, type 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.
- Secondly, press ENTER and AutoFill the formula.
We’ve got 1 as per the explanation above.
- After that, as shown in method 2, Filter the values containing 1 only.
In conclusion, we’ve shown you another combination formula to Filter columns based on another column.
Read More: Excel Filter Data Based on Cell Value (6 Efficient Ways)
5. Filter Column Based on Another Column by Using FILTER Function in Excel
In this method, we’re going to use the FILTER function to Filter columns based on another column.
Steps:
- Firstly, type the following formula in cell B13.
=FILTER(B4:C10,(C4:C10=E5)+(C4:C10=E6),"")Formula Breakdown
- Our array is B4:C10. We’ve 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.
- Finally, we’re not defining any argument in this formula.
- Finally, press ENTER.
In conclusion, we’ve shown the final method of Filtering columns based on another column.
Read More: How to Filter Multiple Columns in Excel Independently
Things to Remember
- Firstly, Remember to use the absolute cell reference.
- Secondly, the FILTER function is only available in Excel 365, and Excel 2021.
Practice Section
We’ve included practice datasets for each method in the Excel file.
Conclusion
We’ve shown you 5 methods of using Excel to Filter column based on another column. If you face any problems, feel free to comment below. Thanks for reading, keep excelling!
