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?
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 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),"")
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.
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.
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.
Testimonials from happy customers
Lynda Marshall6 days ago
Really informative and easy to follow instructions.
Philip Abraham7 days ago
The exercises were really well designed and interesting. I learnt a lot, and am looking forward to part 2.
Hannah Wilson15 days ago
I really enjoyed the training.
Trusted Customer18 days ago
Clear and concise training
Esther Dibiaocha22 days ago
Very good and educative. Quite interactive with so much to learn in a day.
Trusted Customer22 days ago
Sometimes I felt it was going too fast. Otherwise excellent training. I would definitely recommend Happy
Claire22 days ago
Excellent course, good balance of participation - would highly recommend
Anna Whitton26 days ago
Well organised, effective training offer delivered in a good environment. We are already using the tools that we learnt
Ketsela Betru26 days ago
I took two online trainings : Stress Management for Managers and Resilience and Wellbeing at work by your organization. The trainings were lead by Mr Paul. In both cases,...
Trusted Customer26 days ago
We were told that we could ask questions after the training. I did ask a question, but I didn't hear back. Now that it's taking so long to reply, I fear that I am forgett...
Trusted Customer26 days ago
I was kept engaged through out and there was lots of opportunity to ask questions and practice what I was learning. I feel more confident now.
Cheryl Browne28 days ago
Everything from the welcome, the venue and the course delivery was of an excellent quality.