where data is an Excel Table in the range B5:C15.
Problem
The formula below returns a #CALC! error because there are no rows in the data with dates between April 1 and April 30:
This happens because when FILTER returns no data, it returns a #CALC error by default and this error “bubbles up” to SORT. You might try to handle this problem by providing a value for the if_empty argument in FILTER like this: However, this causes the SORT function to return a #VALUE! error, because SORT is configured to sort by column index 2, and there is no second column when the text string “No data” is returned by FILTER:
In other words, when FILTER returns “No data” as a result, the configuration for SORT no longer works.
Solution
One solution to the problem described above is to provide an array constant like this to FILTER as the if_empty argument: The array constant is set up to be a horizontal array by using a comma instead of a semi-colon. The first cell contains “No data” and the second cell is empty. The entire formula looks like this: Now when FILTER returns no data, it returns the message “No data” as a two column array constant, and the SORT function harmlessly “sorts” the array constant by the second column and returns it unchanged to cell E8 where it spills into the range E8:F8:
Array constant size
In general, you should adjust the array constant to match the number of columns in the data supplied to FILTER. For example, if the data had 4 columns, you could use an array constant with 4 values like this: This allows the SORT function to be configured to sort by any column without an error. Also note you can supply whatever values you like in the array constant; empty strings are not required.
Dave Bruns
Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.
