XLOOKUP - don't take the bait!

[albuck125]

If you are in Excel under Office 365 and try to get help using functions HLookup and VLookup, you'll be guided by Microsoft to use the new and improved "XLOOKUP", which according to MS replaces the other two.  DON'T DO IT!   Microsoft fails to warn that XLOOKUP is available ONLY in Office 365 and your sheet will crash if any user with a freestanding version of Excel (even 2019) tries to use it.  What good is a function that can't work in any other version??  This has been pointed out to Microsoft in forums going back at least a year, yet they still make no mention of this issue in the help for these functions.  It cost me two hours of rebuilding spreadsheets that could have been avoided if Microsoft would just have warned, or better yet, disabled the feature given its incompatibility.  

[#2]

@albuck125 Thank you for the heads up! I was about to ask this very question - What happens when you open a spreadsheet in Excel 2019 that was created with Excel 365 using XLOOKUP? - and then came across your post. I first did a general search but no search engine result gave me the answer.

You've answered my unasked question. Thanks.

[#3]

@Marius Pretorius 

IMHO, support articles directly mention that new functionality like XLOOKUP is available only for subscription model:

XLOOKUP function - Office Support (microsoft.com)

and

When do I get the newest features for Microsoft 365? - Office Support

[#4]

Yes, that is true, but I think the point @albuck125 was making is the fact that the XLOOKUP function that you add to your spreadsheet, using Excel 365, will cause a problem when the spreadsheet is opened in Excel 2019. Microsoft fails to warn about that problem.

[#5]

@Marius Pretorius 

If you mean such kind of warning shall be within grid I'm not sure that will be a good idea. It will be very noisy grid. Not clear how deep such backward compatibility warnings shall be (starting from Excel 2007?) and how wide - shall we warn about entire dynamic array concept, what implicit intersection doesn't work any more silently, etc.

IMHO, support documentation which mentions all above shall be enough.

[#6]

@Sergei Baklan I get your point, and I am not going to dispute it. I still appreciated the post of @albuck125 because I was wondering about the compatibility of the said function and he answered my unspoken question. It is not so much about criticism of Microsoft than a heads up to people who may be unaware of the issue. That said, having a function in Excel 365 that is incompatible with Excel 2019 and earlier versions - which I think many people are still using - does make it kind of unusable unless you are sure you are never going to share your spreadsheet.

[#7]

I have Office 2010 on my desktop. Here at work there is a mix of various versions (2010 and up) of desktop Office and Office 365. I've written a macro and embedded the Xlookup function from Github on the code page of a sheet that is using the function so it would be available to anyone who uses the workbook. My question is, if someone were to use it with Excel 365 will it cause a conflict with the native function in Excel 365?

[#8]

To anyone who is interested, I was able to test my Excel (2010 32 bit) sheet with the version of XLookup function found at this link: https://www.exceltip.com/lookup-formulas/custom-excel-xlookup-function.html (I had previously mistakenly said Github, there is an add-in there). I embedded it in the code sheet of the worksheet that uses it on Excel 365 (32 bit). It worked fine and did not conflict with the native XLookup function. I don't believe there would be any issue using it with 64 bit Excel since it isn't an add-in.