Filtering for a value in a list, whether missing or not, is too common in Excel. But the excel filter command can’t do it, we need to use formulas for it. This article will provide you with 4 easy methods to filter missing data in excel.

**Table of Contents**hide

**Download Practice Workbook**

You can download the free Excel workbook from here and practice independently.

**4 Ways to Filter Missing Data in Excel**

Get introduced to our dataset first that represents some salesperson’s sales regions.

**1. Using IF and COUNTIF Functions to Filter Missing Data**

In our very first method, we’ll apply the **IF **and **COUNTIF **functions to filter missing values from a list. If the value is found then it will return “Found” and if not then it will return “Missing”.

**Steps:**

- Activate
**Cell C14**and insert the following formula into it-

`=IF(COUNTIF(C5:C11,B14),"Found","Missing")`

- Then just hit the
**ENTER**button to get the output.

**Formula Breakdown:**

**COUNTIF(C5:C11,B14)**

The **COUNTIF **function will count the number of filtering values along the range **C5:C11**.

**IF(COUNTIF(C5:C11,B14),”Found”,”Missing”)**

Then the **IF **function will return “Missing” for 0 and “Found” for any number greater than zero.

- After that, drag down the
**Fill Handle**icon to copy the formula for the other cells.

Now see, we were filtering for three values and found 1 value and two are missing.

**Read More: ****How to Find Missing Values in Excel (3 Easy Ways)**

**2. Applying IF, ISNA, and MATCH Functions to Filter Missing Data in Excel**

The same previous operation we can perform using the **IF**,**ISNA**,** and MATCH** functions. It will give us the same output as before. You can use **the ISERROR function** instead of the **ISNA **function too, it will result same.

**Steps:**

- Insert the following formula in
**Cell C14**–

`=IF(ISNA(MATCH(B14,$C$5:$C$11,0)),"Missing","Found")`

- Later, press the
**ENTER**button to get the output for the result.

**Formula Breakdown:**

The **MATCH **function will return the row number from the list if the value matches and will return the **#N/A** error if the value doesn’t match.

**ISNA(MATCH(B14,$C$5:$C$11,0))**

Next, the **ISNA **function will return **TRUE **for **#N/A** error and **FALSE **for any other output.

**IF(ISNA(MATCH(B14,$C$5:$C$11,0)),”Missing”,”Found”)**

Finally, the **IF **function will return Missing for **TRUE **and Found for **False**.

- Next, use the
**Fill Handle**tool to copy the formula.

And have a look, we got the same output as before.

**Read More: ****How to Deal with Missing Data in Excel (6 Suitable Ways)**

**Similar Readings**

**How to Compare Two Excel Sheets to Find Missing Data (7 Ways)****Cross Reference in Excel to Find Missing Data (6 Ways)****How to Compare Two Columns in Excel for Missing Values (4 ways)**

**3. Filter Missing Data Using IF, ISNA, and VLOOKUP Functions**

If we use the **VLOOKUP **function instead of the **MATCH** function in the previous formula then we’ll get the same result. Because the **MATCH** and **VLOOKUP **functions work pretty like the same.

**Steps:**

- In
**Cell C14**, apply the following formula-

`=IF(ISNA(VLOOKUP(B14,$C$5:$C$11,0)),"Missing","Found")`

- Finally, just press the
**ENTER**button.

**Formula Breakdown:**

**VLOOKUP(B14,$C$5:$C$11,1)**

Here, the **VLOOKUP **function will return the lookup value if it matches and will return the **#N/A** error if doesn’t match.

- Lastly, apply the
**Fill Handle**tool to insert the same formula for the other regions.

Soon you will get the output like the image below.

**Read More: ****How to Find Missing Values in a List in Excel (3 Easy Methods)**

**4. Using TEXTJOIN, FILTER, and COUNTIF Functions to Filter Missing Data by Comparing Two List**

In the previous methods, we filtered values only from a list. Now we’ll learn a way to filter data from two lists to filter one list’s data to another list using the **TEXTJOIN**, **FILTER**, and **COUNTIF** functions. This formula will directly return the missing region names. For this method, we modified the dataset and used two columns to show the selling regions for two years.

**Steps:**

- To filter for the regions of 2020 in 2021, write the following formula in
**Cell B14**–

`=TEXTJOIN(", ",TRUE,FILTER(C5:C11,COUNTIF(D5:D11,C5:C11)=0))`

- After that, hit the
**ENTER**button and it will return region names. The formula returned Canada two times because Canada remained two times in 2020 and this formula doesn’t merge the same values.

**COUNTIF(D5:D11,C5:C11)=0**

First, the **COUNTIF **function will count the number for each value of the range **C5:C11** through the range **D5:D11** and the output will return as an array. If any value gets equal to zero then it will return **TRUE **and if not then will return **FALSE**.

**FILTER(C5:C11,COUNTIF(D5:D11,C5:C11)=0)**

Later, the **FILTER **function will return the value from the range **C5:C11 **if it matches the criteria.

**TEXTJOIN(“, “,TRUE,FILTER(C5:C11,COUNTIF(D5:D11,C5:C11)=0))**

Finally, the **TEXTJOIN **will join the outputs using a comma.

- Then to filter for the regions of 2021 in 2020, insert the following formula in
**Cell B15**–

`=TEXTJOIN(", ",TRUE,FILTER(D5:D11,COUNTIF(C5:C11,D5:D11)=0))`

- Finally, just press
**ENTER**.

This formula performs the same operation as the previous one. Hope you’ve understood that, if not feel free to comment I’ll try to help then.

**Read More: ****How to Fill Missing Values in Excel (5 Useful Tricks)**

**Conclusion**

That’s all for the article. I hope the procedures described above will be good enough to filter missing data in excel. Feel free to ask any question in the comment section and please give me feedback. Visit **ExcelDemy** to explore more.