Forum Discussion
XLOOKUP - don't take the bait!
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.
7 Replies
- 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?
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.
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.
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
- 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.
