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.

**How to Filter Missing Data in Excel: 4 Easy Ways**

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

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

The same previous operation can be performed 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

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

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

**Download Practice Workbook**

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

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

## Related Articles

- How to Find Missing Rows in Excel
- How to Count Missing Values in Excel
- How to Compare Two Excel Sheets to Find Missing Data
- How to Cross Reference in Excel to Find Missing Data
- How to Remove Missing Values in Excel

**<< Go Back To Missing Values in Excel | Data Cleaning in Excel | Learn Excel**