Md. Sourov Hossain Mithun
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.
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.
