ajFilter function
Description
The ajFilter function filters a range of data based on the given criteria and extracts the records that meet this criteria.
Syntax
ajFilter(in_array, include, [if_empty], [convert_to_text])
| Argument Name | Argument Type | Description |
|---|---|---|
| in_array (required) | Range / Array | The range of cells to be filtered. |
include (required) | Range / Array | A range of cells that specifies the condition to filter the range of cells specified in ‘In_array’. 1 means including the row while 0 means excluding the row. The number of rows in Include range must match the number of rows in In_array. |
| if_empty (optional) | Single Cell / Single Value | The message to be shown when none of the rows meets the condition. The default value is an empty string (“”). |
| convert_to_text (optional) | Boolean | If it equals TRUE, the return result will be converted to string values. If it equals FALSE, the return result will preserve the original data type. The default value is FALSE. |
The function will return:
1) Return Value: Filtered records
2) Return Type: Multiple values (array formula)
Example
Example 1
As you can see from the above example, the result in cell array B11:E14 is filtered with the condition range F2:F5, which is using the condition formula '{=C2:C5<=3} to calculate the condition if the row should be filtered out or not. Therefore, the filter result for C2 is false and the filter results for others are true. Thus, the row B2:E2 has been excluded.
Example 2
Similar with example 1, but has changed all input conditions to False so that no data can be filtered. The value of if_empty would be shown when no data was filtered.
Click here to download the use case workbooks for further reference.
Error Scenarios
It will return #VALUE! when missing any required parameter or mismatch parameter type. Besides, system will raise error for below scenario(s).
| Error Scenario |
|---|
| In array is not array data type. |
| Include is not array data type. |