Forum Discussion
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
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!
- the fix was =IFERROR(FILTER(Example!M:M,(Example!B:B=$B$5)*(Example!G:G=C5)), "")
