How to Use the Excel Dynamic Array FILTER Function

In: BlogDate: Mar 28, 2022By: Sal Agoro

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?

More about Happy

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?

  1. FILTER returns multiple match results for more than one LOOKUP value.
  2. Unlike advanced filter in Excel, the FILTER function allows you to filter without having to refresh your filter.
  3. FILTER can be nested in other functions such as putting filters inside the AND, COUNTA, OR, or SUM functions.
  4. 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 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, available either online or face-to-face.

Why not sign up to our newsletter?

Sign up to our monthly newsletter, full of tips, tricks and news to help you to be happier and more productive at work.

Sign up here

Improve your productivity in Excel with happy

Happy has high-quality, learner-focused Excel training courses for all skill levels, designed to improve your confidence and productivity. And our new online learning programme is just as interactive as our classroom sessions.

Here are our most popular options:

Our learners tell us that they save an average of 32 minutes a day with our Excel training courses. How much time could you save with us?

Why learn online with Happy?

  • Interactive and engaging - just like our classroom sessions
  • Bitesize or full-day sessions - fit around your schedule
  • Learn from home - all you need is a quiet place to call from and an internet connection
  • IT Helpline - 2 years of free support after your course
  • No quibble money-back guarantee

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.

More by Sal

Testimonials from happy customers