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.

Replay any session. Every minute covered.