New Contributor

# FILTER with multiple conditions and exclude #NA

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

# Re: FILTER with multiple conditions and exclude #NA

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

# Re: FILTER with multiple conditions and exclude #NA

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:

=IF(ISNA(FILTER(Example!M:M,(Example!B:B=\$B\$5)*(Example!G:G=C5))),"",FILTER(Example!M:M,(Example!B:B=\$B\$5)*(Example!G:G=C5)))

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!