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.

- Press
**Enter**.

## Practice Section

We’ve included practice datasets for each method in the **Excel** file.

**Download Practice Workbook**

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