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.
MAX returns the highest sales figure from Total. This is then looked up in the same column, and the adjacent month returned.
=ππ»πΎπΎπΊππΏ(πΌπ°π(ππππππππ[πππππ]),ππππππππ[πππππ],ππππππππ[πΌππππ])
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.
.png)
