change an array

[pboltonchina]

Hello everyone, long time since I've been here but last time a gentleman gave me a perfect solution to my problem and it worked great.
I have just cancelled my Office 365 subscription and purchased Office 2019 Professional Plus (genuine) and installed it. The problem is that all the formulas my sheet uses, and were adaptable as my validation list changed are now Arrays that I can't edit. This is the fomula
=_xlfn.XLOOKUP(_xlfn._xlws.FILTER('Meals 2'!$B$2:$D$998,'Meals 2'!$A$2:$A$998=record!B22),'Meals list'!$A$1:$A$1113,'Meals list'!$B$1:$B$1113)
but it now has 2 wavy brackets around it { }. my sheet is now useless unless I get a solution.
As always any help is greatly appreciated. Regards
Paul

 

[DRSteele]

Realising this will enrage you, I suggest you use Microsoft 365. I hope you can get a refund for your seven year old Excel 2019. All the new functions are invaluable (as you noticed by actually using them and now regretting abandoning).

 

[Sergius]

Hello! After all, you can update up to 2021 versions, where most functions will work. Due to the fact that you have officially purchased 2019, it will be one of the best solutions.

 

[pboltonchina]

Hi,
Thanks for the replies, I'll see if I can upgrade to 2021. Is there nothing I can do in 2019 to achieve the same objective?
Regards
Paul

 

[pboltonchina]

Looks like the upgrade is quite complex to do and as I only have 1 file that needs the 365 function I would rather do it manually tha pay £80 a year.
Thanks anyway.
Regards
Paul

 

[Sergius]

Your problem can be solved in 2019 versions. The formula will certainly be a little more complicated, but this is the costs of the version. In any case, you can always ask for help in solving your problem.

 

[pboltonchina]

Is that something you could help with?

 

[Sergius]

Create a new topic, imagine a data fragment and describe what you need.

 

[ebea]

If it's only one file, then I would try with this formula:
=INDEX('Meals list'!$B$1:$B$1113, MATCH(record!B22, 'Meals list'!$A$1:$A$1113, 0))

If you want the FILTER function to work, I think the best way would be to have a helper column, to filter out.

 

[pboltonchina]

How would I use a helper column, I've not come across that before?
Regards
Paul