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 “Departmentcolumn.

excel filter column based on another 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.

excel filter column based on another column

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

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.

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.

Related Content: Filter Multiple Criteria in Excel (4 Suitable Ways)


Similar Readings


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

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.

excel filter column 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!


Related Articles

Rafiul Haq

Rafiul Haq

Greetings! I am Rafiul, an experienced professional with an engineering degree and an MBA in finance. Data analysis is my passion, and I find Excel to be an invaluable tool in this domain. My goal is to simplify complex concepts by providing comprehensive and user-friendly guides on Excel and finance topics, aiming to enhance people's productivity and understanding at ExcelDemy.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo