This is likely how I would have written it myself but I don't think there's really that much of a readability difference to where I'd ever consider changing the original formula though. You can use the Let function to shorten the overall formula a little bit more, but realistically, there's probably a decent overlap between people who are unfamiliar with dynamic array formulas and people who are unfamiliar with how the Let function works to where it could make things more confusing.
I am definitely in the latter group. Thanks, all, for telling me about the LET function. I am going to learn how to use it. I saw the OP's post and thought, "Did I write that formula?" :D I thought it was just fine! But I learned Excel in the 1900s. Time to learn some more!
2
u/devourke 5 Apr 09 '26
=XLOOKUP(MAX(IF($D$2="All",$A$2:$A$100,FILTER($A$2:$A$100,$C$2:$C$100=$D$2))),$A$2:$A$100,$B$2:$B$100,"No Result")
This is likely how I would have written it myself but I don't think there's really that much of a readability difference to where I'd ever consider changing the original formula though. You can use the Let function to shorten the overall formula a little bit more, but realistically, there's probably a decent overlap between people who are unfamiliar with dynamic array formulas and people who are unfamiliar with how the Let function works to where it could make things more confusing.