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**.

**Table of Contents**hide

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

- 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!