XLOOKUP in older (non Office 365) Excel versions

Hi
Does anyone have a solution for this: I've been sending a workbook with a workings sheet containing raw calculations using XLOOKUP formulae that feeds into a summary calculated other front sheet. I am using Office 365 (app not web). When the workbook is sent to certain colleagues and they open and click 'enable editing' so they can make changes, the front summary sheet numbers disappear and appears blank to them. Apparently this is to do with the XLOOKUP being a new formula and not compatible with older Excel versions. Is this the reason? So far I have sent a workbook, replacing the calculation sheet formulae with values so colleagues can at least see the summary totals. This isn't ideal because they need to make changes to the inputs and see them calculate through to the summary. Is there a workaround for this issue? Thanks

Thanks for quick response! Not sure if you were proposing re-working the formulae - it's not really an option e.g. with index/match or something as it's a big spreadsheet with lots of complex formulae. I wondered if there was a workaround. Here's a couple of the shorter formulae:
=IF([@REFERENCE]<>"",XLOOKUP([@REFERENCE],LIBRARY[CODING],LIBRARY[NAME],"error",0,1),"")
=XLOOKUP([@COLOUR],CATALOGUE[MAJOR],CATALOGUE[MINOR],"ERROR",0,1)

Thanks

Questray

I don't know if I understand your problem, but for the previous version I use this way to replace XLOOKUP

=IFERROR(VLOOKUP([@COLOUR],CHOOSE({1\2},CATALOGUE[MAJOR],CATALOGUE[MINOR]),2,1),"Error")

Decio

Originally Posted by Sam Capricci

Why do you believe and Index/Match couldn't be substituted to do what an xlookup does? Could you post a sample workbook (instructions at the top of the post in yellow)? It is not easy to diagnose a formula that uses table names instead of cell and cell references.

Sure, yes I see - the workbook is too hefty/involved and I haven't time to mock up an example version! Thanks

Originally Posted by deciog

Questray

I don't know if I understand your problem, but for the previous version I use this way to replace XLOOKUP

=IFERROR(VLOOKUP([@COLOUR],CHOOSE({1\2},CATALOGUE[MAJOR],CATALOGUE[MINOR]),2,1),"Error")

Decio

Yes, thanks for that - agreed. The issue is a massively complex workbook - that took a good few weeks to build apparently and no time to re-work it all at the moment.

Originally Posted by MrShorty

@AliGW: I wouldn't say that -- you could always tell those colleagues that they will just have to upgrade to a version that supports XLOOKUP(). (I wonder if you could figure out how to arrange a kickback from Microsoft for incorporating new functions then using that as leverage to force colleagues to buy the newest versions...).

As for the OP, I don't know of any quick and easy workarounds. I see two possible solutions -- either your colleagues need access to the same or newer version that you are using, or you need to rewrite your spreadsheet to be backwards compatible. I don't (yet) see any other options for XLOOKUP() compatibility.

Cheers: this is what I was trying to establish - if any clever bods had worked out a way to deal with it outside of re-working formulae but I see there isn't apart from exactly - colleagues need to upgrade. I find XLOOKUP so much simpler for me anyway than INDEX-MATCH. Which is the oldest Excel version that supports XLOOKUP?

Ok, thanks AliGW for clarification about 365 - I did not know that. Also noted re: quotes. Resolved. Thanks all!