πŸ’₯ EVENT RECORDING PACKAGES NOW AVAILABLEΒ ON-DEMAND! πŸ’₯
Why FILTER instead of XLOOKUP?
Resources

Why FILTER instead of XLOOKUP?

Lookups have long been a staple of Excel, whether VLOOKUP, INDEX & MATCH or the newer XLOOKUP.

So many daily tasks depend on them, and their use is ubiquitous in homes and offices across the length and breadth of the world.

The problem is they can be overused β€” and getting blasΓ© about their limitations can be costly.

One of them is the single matching row restriction. Sometimes, this is what you want, but other times it's not.

‍

In the video example, a table called tblSales houses sales figures for a calendar year.

We want to find the month that has the highest total. There are a couple of ways we can do this.

Method 1

MAX returns the highest sales figure from Total. This is then looked up in the same column, and the adjacent month returned.

=πš‡π™»π™Ύπ™Ύπ™Ίπš„π™Ώ(π™Όπ™°πš‡(πšπš‹πš•πš‚πšŠπš•πšŽπšœ[πšƒπš˜πšπšŠπš•]),πšπš‹πš•πš‚πšŠπš•πšŽπšœ[πšƒπš˜πšπšŠπš•],πšπš‹πš•πš‚πšŠπš•πšŽπšœ[π™Όπš˜πš—πšπš‘])

Method 2

FILTER returns the months with a sales figure equal to the maximum in Total.

=π™΅π™Έπ™»πšƒπ™΄πš(πšπš‹πš•πš‚πšŠπš•πšŽπšœ[π™Όπš˜πš—πšπš‘],πšπš‹πš•πš‚πšŠπš•πšŽπšœ[πšƒπš˜πšπšŠπš•]=π™Όπ™°πš‡(πšπš‹πš•πš‚πšŠπš•πšŽπšœ[πšƒπš˜πšπšŠπš•]))

It's evident that Method 2's FILTER is better, as there is the possibility of repetition, which XLOOKUP doesn't cater for.

There's also a broader point in all this.

Sometimes data is favourable. Other times it's messy, ugly, obscure, and full of anomalies.

Just because a formula works in one instance doesn't mean it'll work in another. That's why stress-testing your formulas is vital for achieving accuracy and factuality in different scenarios.

About the Author

Andrew is a technical writer who specialises in Microsoft Excel and data analysis. He has published hundreds of articles and social media posts aimed at helping people enhance their skills and spreading good practices.

Conversation

0 Comments

As you comment, let's keep the discussion friendly and respectful to add value to the conversation.

Thank you! Your comment has been sent!
Oops! Something went wrong while submitting the form.
Commenter profile pic.
Author Name
Comment Time

Lorem ipsum dolor sit amet, consectetur adipiscing elit. Suspendisse varius enim in eros elementum tristique. Duis cursus, mi quis viverra ornare, eros dolor interdum nulla, ut commodo diam libero vitae erat. Aenean faucibus nibh et justo cursus id rutrum lorem imperdiet. Nunc ut sem vitae risus tristique posuere. uis cursus, mi quis viverra ornare, eros dolor interdum nulla, ut commodo diam libero vitae erat. Aenean faucibus nibh et justo cursus id rutrum lorem imperdiet. Nunc ut sem vitae risus tristique posuere.

Reply
Delete
Commenter profile pic.
Author Name
Comment Time

Lorem ipsum dolor sit amet, consectetur adipiscing elit. Suspendisse varius enim in eros elementum tristique. Duis cursus, mi quis viverra ornare, eros dolor interdum nulla, ut commodo diam libero vitae erat. Aenean faucibus nibh et justo cursus id rutrum lorem imperdiet. Nunc ut sem vitae risus tristique posuere. uis cursus, mi quis viverra ornare, eros dolor interdum nulla, ut commodo diam libero vitae erat. Aenean faucibus nibh et justo cursus id rutrum lorem imperdiet. Nunc ut sem vitae risus tristique posuere.

Reply
Delete

NOWΒ AVAILABLE ON-DEMAND.