change an array

pboltonchina

Well-known Member
Joined
Apr 24, 2008
Messages
1,124
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
 
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).
 
Last edited by a moderator:
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top