Subscriber only lesson.
Sign up to this course to view this lesson.
About this lesson
Using FILTER() to restrict the results of a Dynamic Array based on a single condition
Download the ‘before’ and ‘after’ Excel workbooks from the video tutorial and try the lesson yourself.Single Condition DA FILTERing.xlsx
48 KB Single Condition DA FILTERing - Complete.xlsx
FILTER-ing Dynamic Arrays with a Single Condition
Working with the FILTER function to reduce the results of a dynamic array.
When to use
When you need to filter certain items out of your dynamic array results.
- Dynamic arrays were released to Microsoft 365 AFTER Excel 2019 was released
- This means that you must have a version of Excel newer than Excel 2019 to use them
- Can be used with table components or ranges
- Syntax: =FILTER( array, include, [if_empty] )
- The array parameter can have any number of rows/columns
- The “include” formula must be a single column (row) compared to a value
- The “include” formula must have the same number of rows (columns) as the original array
- Returning the rows from the Sales table where the Category = Beer:
- =FILTER( Sales, Sales[Category] = "Beer" )
- Returning a block of data where the value in column E = 10
- =FILTER( A10:C30, C10:C30 = 10 )
Operators for FILTER inclusion
- = Include result in array output if array item is equal to item
- < Include result in array output if array item is less than item
- > Include result in array output if array item is greater than item
- <> Include result in array output if array item is NOT equal to item
- <= Include result in array output if array item is less than or equal to item
- >= Include result in array output if array item is greater than or equal to item
Lesson notes are only available for subscribers.