Oct 13 2020
09:49 AM
- last edited on
Feb 01 2023
11:16 AM
by
TechCommunityAP
Oct 13 2020
09:49 AM
- last edited on
Feb 01 2023
11:16 AM
by
TechCommunityAP
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.
Dec 30 2020 10:58 AM
@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.
Jan 03 2021 03:55 AM
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
Jan 07 2021 11:52 AM
Jan 08 2021 12:07 PM
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.
Jan 09 2021 12:15 PM
@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.
Sep 28 2021 10:22 AM
Oct 19 2021 01:38 PM
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.