Converting Nested Array formula from Sheets to Excel
Ive been working on a project for my work that originally started in Sheets but now has to be converted to Excel to be integrated. When i imported the sheet into Excel i discovered all the big formula arrays I had been provided and edited myself to add the XLOOKUP to it were broken in Excel due to using nested arrays.
Is there a way to convert my Sheets formula into a Excel compatible formula?
Edit for context: When the current formula is in Excel it is just showing a hover popup with "Nested Arrays are not supported" and the cell text shows ####
This is the formula used at the moment which has this formula into the left most cell of the timeline sheet and it automatically puts the "x" into the related cells it correlates too
=let( prior, XLOOKUP($B4,'ReferenceSheet'!A:A,'ReferenceSheet'!F:F), duration, XLOOKUP($B4,'ReferenceSheet'!A:A,'ReferenceSheet'!E:E), endTimes, XLOOKUP($B4,'ReferenceSheet'!A:A,'ReferenceSheet'!B:B), timeline, $D$1:$DZ$1, map(endTimes, lambda(end, if(end="",, map(timeline, lambda(t, if(isbetween(t, end-prior, end-prior+duration, true, false), "x",))))))) The whole thing creates a timeline of operation periods within a 24 hour period by referencing some start and duration times within the ReferenceSheet and checks what the target name is, Currently this line is from the B4 row but its designed so that whatever row I have and the name of the operation is changed, the formula adjusts the periods automatically.
Its really the part about making it a non nested array formula which is what is causing the issues with the converting from Sheets to Excel
This is an example of it working within Sheets. Just with some names and other related information removed
Any advice or suggested changes to the formula would be greatly appreciated
[link] [comments]
Want to read more?
Check out the full article on the original site