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.