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

Get FREE Advanced Excel Exercises with Solutions!

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.

excel filter column based on another column


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

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.

excel filter column based on another column

Thus, the Name column is Filtered based on another column.


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)=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.

excel filter column based on another column

  • Thirdly, press CTRL + ENTER.

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

Now, we’ll Filter the values.

  • Firstly, select the cell range B4:D10.
  • Secondly, from the Data tab >>> select Filter.

excel filter column based on another column

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.

excel filter column based on another column

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


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.

excel filter column based on another column

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

We can see there are 3 TRUE values.

excel filter column based on another column

In conclusion, we’ve shown you a combination formula to Filter columns based on another column.


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.

excel filter column based on another column

  • Secondly, press ENTER and AutoFill the formula.

We’ve got 1 as per the explanation above.

In conclusion, we’ve shown you another combination formula to Filter columns based on another column.

excel filter column based on another column


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.

excel filter column based on another column


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.


Download Practice Workbook


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!


<< Go Back to Data | Filter in Excel | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Rafiul Haq
Rafiul Haq

Rafiul Haq worked as an Excel and VBA Content Developer in Exceldemy for over two years and published almost 200 articles for the website. He is passionate about exploring new aspects of Excel and VBA. He received his Bachelor of Science in Mechanical and Production Engineering (MPE) from the Islamic University of Technology. Rafiul furthered his education by obtaining an MBA in Finance from the Institute of Business Administration (IBA) at the University of Dhaka. Apart from creating... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo