XLookup Vs. Index/Match

I’ve started playing around with XLOOKUP(). I find it intuitive and effective.

The problem with any new Excel function though is compatibility. If you use a new Excel function in your model and then plan to share your model at any point (such as to clients, investors, lenders, etc), you run the risk that the person you share the model with is using an older version of Excel.

We run into this with our Library of Excel models on A.CRE. Our models are largely only compatible with Excel 2013 and later since we use functionality in our models that was not available prior to the 2013 version. We do our best to communicate this fact to our readers, but we still regularly get messages from people saying “the file is corrupt”. When in reality, it’s an incompatibility issue.

So I recommend learning new functions such as XLOOLUP() and even using them when your stakeholders are all using the latest version of Excel. But if you plan to share the file more broadly, I recommend adding “backward compatibility” by sticking to the legacy functions.

Thanks for the great question!