How to Use the Excel Dynamic Array FILTER Function

What do you do if you want to Lookup multiple criteria that returns all match results, not just the first match? Then the new Excel Dynamic Array FILTER formula will come to the rescue. It will take you to the future! This function is only available to Office 365 subscribers.

Hi, we are Happy

We are leading a movement to create happy, empowered and productive workplaces.

How can we help you and your people to find joy in at least 80% of your work?

Keep informed about happy workplaces

Sign up to Henry's monthly Happy Manifesto newsletter, full of tips and inspiration to help you to create a happy, engaged workplace.

The new FILTER formula is like the automatic version of Excel’s advanced filter feature but even better.

Basically the Excel Filter function filters your data based on criteria you define. It then returns all the matching records based on other conditions.

Why should you use the Excel FILTER function?

FILTER returns multiple match results for more than one LOOKUP value.
Unlike advanced filter in Excel, the FILTER function allows you to filter without having to refresh your filter.
FILTER can be nested in other functions such as putting filters inside the AND, COUNTA, OR, or SUM functions.
It is super easy to write one: =FILTER(array, include, [if_empty])

FILTER is only available in the latest version of Excel, so you will need an Office 365 subscription to use it.

How does it work?

The FILTER function will return an array, which will spill if it’s the final result of a formula. This means that Excel will dynamically create the appropriate sized array range when you press ENTER. If your dataset has the potential of returning an empty value, then use the 3rd argument ([If empty]). Otherwise, a #CALC! error will result.

FILTER Function Used to Return One Criteria

In the following example we used the formula =FILTER(A5:G21,D5:D21=L2,””) to return all records for Violin, as selected in cell L2, and if there are no Violins, return an empty string (“”).

FILTER function used to return one criteria

FILTER Function Used to Return Multiple Criteria

In this case, we’re using the multiplication operator (*) to return all values in our array range (A5:G21) that have Violin AND are in the Retail channel: =FILTER(A5:G21,(D5:D21=L2)*(B5:B21=L1),””)

FILTER function used to return multiple criteria

Want to learn more?

If you want to become an expert in Excel, creating complex formulas or doing more complex analysis, you’ll definitely need to learn more about dynamic array functions. Join us on our Expert Excel Core Level 4 course to learn more online via Zoom on 20th February 2024.

Learn the 10 core principles to create a happy and productive workplace in Henry Stewart's book, The Happy Manifesto.

Support your aspiring and current managers to be empowering and confident leaders with Happy

Happy offers leadership programmes at Level 3, Level 5 and Level 7, from new managers/supervisor level all the way up to senior leadership teams and CEOs. These programmes are based on the ideas of trusting your people. They are practical and based on applying what yo’ve learnt. We aim to inspire and ignite change in your organisation, as well as giving you valuable management skills such as business strategy, decision-making, negotiation and project management.

We also offer programmes tailored specifically to people from Global Majority backgrounds. The content is the same, but have been designed to give new and experienced managers the skills they need to navigate organisational culture with a clearer perspective on their own potential, as well as building their confidence and expanding their professional strengths.

Sal Agoro

Sal trains most MS Office packages. Her real love is in transferring knowledge by making her training sessions fun and more impactful. She is Happy’s Outlook Champion and ensures Outlook courses and training material are updated regularly with new features. Sal has been at Happy for over three years.