FILTER with multiple conditions and exclude #NA

Copper Contributor

Hello, I will try to keep it simple.


I have a filter that brings up a select data from a drop down menu. These rows can be between 1-20 in length with the =FILTER(Example!M:M,(Example!B:B=$B$5)*(Example!G:G=C5)) 


I have a number column from 1-20 to accommodate the max possible information, but sometimes when it is only 1 row of info, I get 19 rows with #NA, I would like to remove these to show blanks, how do I adjust the formula above to do this?




2 Replies
the fix was =IFERROR(FILTER(Example!M:M,(Example!B:B=$B$5)*(Example!G:G=C5)), "")


Another way to replace #N/A errors with blank cells is to use a combination of the IF and ISNA functions.

Here’s an example of how you could do this:


In this example, the ISNA function checks if the result of the FILTER function is an #N/A error. If it is, the IF function returns an empty string ("") to display a blank cell. If the result of the FILTER function is not an #N/A error, the IF function returns the result of the FILTER function.

This should replace any #N/A errors returned by the FILTER function with blank cells.

I hope this helps!