XLOOKUP or FILTER to find the highest sales?

There are numerous ways of finding the highest value in a column and returning another in the matching row. Let’s have a look at two.

In the example, a table called tblSales is present on the left. Method 1 uses an XLOOKUP statement to find the maximum figure in the Total column and returns the adjacent month.

This works — sort of. It’s best when there are no duplicates, as only the first matching row is considered. In this case, March is returned whereas July is ignored as it appears after.

You could set the [𝚜𝚎𝚊𝚛𝚌𝚑_𝚖𝚘𝚍𝚎] argument to -1 so the lookup starts from the bottom, meaning July would take precedence.

Method 2, however, uses FILTER to find out the same thing, except it returns both March and July. That is the benefit of this function over XLOOKUP — it handles multiple results.

The takeaway from this is to be aware of this type of scenario so you’re using the right function for your needs.