Converting Nested Array formula from Sheets to Excel
Our take
The challenge of converting complex formulas from Google Sheets to Excel is a common hurdle for many users navigating the evolving landscape of spreadsheet technology. In the case of the user who shared their experience with nested array formulas, the transition highlights not only a technical issue but also the broader implications of adapting to different platforms that employ varied functionalities. As spreadsheet users increasingly seek innovative solutions to enhance their productivity, understanding the nuances of these tools becomes imperative. This need for comprehension is echoed in related discussions, such as those found in articles like ArrayFormula syntax translation from Google Sheets -> Excel and Is there a way to reference an array of arrays?, which explore similar challenges faced by users trying to maximize the capabilities of both platforms.
The reported issue of encountering "Nested Arrays are not supported" in Excel underscores a significant difference between how these two spreadsheet applications handle array formulas. While Google Sheets allows for the use of nested arrays, Excel has specific limitations that can hinder the seamless transition of complex workflows. This situation serves as a reminder that while both tools are powerful, they are not interchangeable in all scenarios. Users must be prepared to adapt their approaches, particularly when leveraging advanced functions like XLOOKUP within nested arrays. The ability to translate these formulas effectively is crucial for maintaining productivity and ensuring that users can continue their data operations without interruption.
Moreover, the inherent complexity of the formula presented—utilizing functions like LET and LAMBDA—demonstrates the advanced capabilities available in modern spreadsheet applications. These innovations empower users to create dynamic and responsive spreadsheets. However, they also necessitate a deeper understanding of how to construct formulas that are compatible across different platforms. The journey from Google Sheets to Excel doesn't just involve a simple copy-paste; it requires a methodological approach to rethinking how formulas are structured. This need for adaptability is echoed in user queries about optimizing their data management processes, as seen in discussions such as I am trying to prevent the end user from having to insert data twice to see it on two different sheets.
As spreadsheet technology continues to evolve, users must embrace a growth mindset while learning to navigate these tools effectively. The shift from Google Sheets to Excel offers an opportunity to rethink data management strategies and explore new methodologies for working with formulas. Moving forward, it will be essential for users to stay informed about updates and features that enhance compatibility between platforms. Encouraging community engagement and sharing insights will foster a collaborative environment where users can learn from each other’s experiences.
In conclusion, the challenge of converting nested array formulas from Sheets to Excel serves as a microcosm of the broader evolution in spreadsheet technology. As users continue to seek transformative solutions, the focus should remain on empowering them to explore innovative approaches to data management. How users adapt to these changes will ultimately shape their productivity and success in the evolving landscape of spreadsheet technology. The question remains: as we move toward a more integrated future, how can spreadsheet applications better support seamless transitions between platforms?
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]
Read on the original site
Open the publisher's page for the full experience
Related Articles
- "ArrayFormula" syntax translation from Google Sheets -> ExcelHi there. Im trying to understand some differences between Sheets / Excel. In Google Sheets I will use the alternate array formula: (Ex. {cellvalueA, cellvalueB} as a way to populate two cells with one function. I use this to autofill information from separate tabs across ranges from TabA to TabB. I will minimize "Column A", and have the information auto-populated in "B' for 'default info' (from TabA to TabB), but can then be manually overwritten (in Tab B) without destroying the function. How do I do the same thing in excel? Is there a different syntax or formula I can use to achieve this? Because the "{}" doesn't work the same as far as I can tell. Specifically what I need is the ability to use a formula to populate into a cell without the formula residing in that destination cell. Let me know if additional context is need to answer my inquiry. submitted by /u/SmallBlackMen [link] [comments]
- Is there a way to reference an array of arrays?Is there a way to get Excel to use an array of arrays in in a formula? Instead of writing a formula that references an array and dragging it down through a column, can I write the formula in a way that results in an output of SUM(O2#),SUM(O3#),SUM(O4#),SUM(O5#),...,SUM(O1000#)}? I work with enormous data sets, but they vary in size. Since I need the workbooks to work with any amount of data, I end up dragging formulas down extra far, leaving rows that just output empyy values when the data doesn't reach that far. If I can make the formula reference an array of arrays then I could eliminate all those garbage rows. Thank you. Unfortunately, because a security restrictions, I can't use macros or VBA at all. I need to do it everything through standard excel syntax. EDIT: Sorry, I didn't make it clear that I used SUM() as an example to try and simplify and generalize. Here's a more explicit example of what I'm doing: ID Data Col C Col D A 14 =UNIQUE(ID#) =IF($C1="","",LET(ArrSt1,TRIMRANGE(DROP($B:$B,4),2),ArrSt3,TRIMRANGE(DROP($H:$H,4),2),ArrA,(FILTER(ArrSt3,ArrSt1=C2,)),TRANSPOSE(FILTER(ArrA,ArrA<>"")))) A 23 =IF($C2="","",LET(ArrSt1,TRIMRANGE(DROP($B:$B,4),2),ArrSt3,TRIMRANGE(DROP($H:$H,4),2),ArrA,(FILTER(ArrSt3,ArrSt1=C2)),TRANSPOSE(FILTER(ArrA,ArrA<>"")))) A 42 =IF($C3="","",LET(ArrSt1,TRIMRANGE(DROP($B:$B,4),2),ArrSt3,TRIMRANGE(DROP($H:$H,4),2),ArrA,(FILTER(ArrSt3,ArrSt1=C3,)),TRANSPOSE(FILTER(ArrA,ArrA<>"")))) B 1 =IF($C4="","",LET(ArrSt1,TRIMRANGE(DROP($B:$B,4),2),ArrSt3,TRIMRANGE(DROP($H:$H,4),2),ArrA,(FILTER(ArrSt3,ArrSt1=C4,)),TRANSPOSE(FILTER(ArrA,ArrA<>"")))) B 2 =IF($C5="","",LET(ArrSt1,TRIMRANGE(DROP($B:$B,4),2),ArrSt3,TRIMRANGE(DROP($H:$H,4),2),ArrA,(FILTER(ArrSt3,ArrSt1=C5,)),TRANSPOSE(FILTER(ArrA,ArrA<>"")))) B =IF($C6="","",LET(ArrSt1,TRIMRANGE(DROP($B:$B,4),2),ArrSt3,TRIMRANGE(DROP($H:$H,4),2),ArrA,(FILTER(ArrSt3,ArrSt1=C6,)),TRANSPOSE(FILTER(ArrA,ArrA<>"")))) B =IF($C7="","",LET(ArrSt1,TRIMRANGE(DROP($B:$B,4),2),ArrSt3,TRIMRANGE(DROP($H:$H,4),2),ArrA,(FILTER(ArrSt3,ArrSt1=C7,)),TRANSPOSE(FILTER(ArrA,ArrA<>"")))) B 90 =IF($C8="","",LET(ArrSt1,TRIMRANGE(DROP($B:$B,4),2),ArrSt3,TRIMRANGE(DROP($H:$H,4),2),ArrA,(FILTER(ArrSt3,ArrSt1=C8,)),TRANSPOSE(FILTER(ArrA,ArrA<>"")))) B 94 =IF($C9="","",LET(ArrSt1,TRIMRANGE(DROP($B:$B,4),2),ArrSt3,TRIMRANGE(DROP($H:$H,4),2),ArrA,(FILTER(ArrSt3,ArrSt1=C9,)),TRANSPOSE(FILTER(ArrA,ArrA<>"")))) =IF($C10="","",LET(ArrSt1,TRIMRANGE(DROP($B:$B,4),2),ArrSt3,TRIMRANGE(DROP($H:$H,4),2),ArrA,(FILTER(ArrSt3,ArrSt1=C10)),TRANSPOSE(FILTER(ArrA,ArrA<>"")))) ... =IF($C1000="","",LET(ArrSt1,TRIMRANGE(DROP($B:$B,4),2),ArrSt3,TRIMRANGE(DROP($H:$H,4),2),ArrA,(FILTER(ArrSt3,ArrSt1=C1000,)),TRANSPOSE(FILTER(ArrA,ArrA<>"")))) Output: ID Data Col C Col D Col E Col F F A 14 A 14 23 42 A 23 B 1 2 90 94 A 42 "" B 1 "" B 2 "" B "" B "" B 90 "" B 94 "" "" ... "" There are a lot of data manipulations omitted, so if something seems weird (like the empty cells in Data Col) please ignore it. The questions is how to eliminate the unneeded rows in Col D. Thank you. EDIT2: Studying the BYROW, SCAN, and MAP suggestions, led to this page on Exceljet, https://exceljet.net/glossary/array-of-arrays , that suggests my question is a known problem in Excel's engine and there is no work-around. Thank you to everyone who tried to help. submitted by /u/TwitchyDingo [link] [comments]
- I am trying to prevent the end user from having to insert data twice to see it on two different sheets, and the formulas I've been trying aren't copy/pasting well.I have been making a CRM spreadsheet for a finance business with over 100 clients. In trying to optimize the formatting, I have developed two different views which have two different uses. Sheet 1 is "2026 List View" where the informaiton is all on one row per client. It is annoying to side scroll to see all of the information, but it gives the best overall view as to who has paid. https://preview.redd.it/1yinsuhafikg1.png?width=3370&format=png&auto=webp&s=7559a5cfc76b671e2bbcc4b4700c446e2531ce46 Sheet 2 is "2026 Grid View" where the informaiton is organized differently to not need side-scrolling, and gives a better quick per-client view. https://preview.redd.it/2oip1qrbfikg1.png?width=2697&format=png&auto=webp&s=7b4f9f29f34db95d14e17df60efca580ff4c546a I am writing formulas for Sheet 2's cells to reference Sheet 1's data. For example, Sheet 2's cell B3 has ='2026 List View'!A2 and it is working well in terms of the formula. However, once I finished two tables refrerencing cells in rows 2 and then 3, copy/pasting made the next table do the correct columns but in the rows 16 and 17 instead of 3 and 4. I tried continuing the pattern longer, but copy/pasting still made it skip several rows though it is recognizing the correct columns. Is there a different formula I could use to make the copy/pasting more successful? Or, am I stuck doing this cell by cell for 100 rows, making it not worth the hassle? submitted by /u/urgrlB [link] [comments]
- Cell merging / formatting formulasThis might be an odd one. I'm not that skilled with excel as my use of in within my job is pretty limited. However, I tend to use this template my predecessor made to summarize data from our program. Works well, just a simple ='SHEET 1'!A1 for all cells. The first two images give an example. After the data is ported, I have to get rid of the zeros between the data and write system names. When it comes to pasting it on letters, the names are bolded, upped a font size, and two of the cells are merged (3rd image) This gets a bit tedious as the lists can get pretty long so I've been trying to figure out how to streamline it on my own. My idea has so far has been to have a separate cell detect when I'm finished adding my data and then format the aforementioned cells (4th image). For the life of me, just can't figure out how to write a formula to do it. What I would need is for the formula to detect a 1 (could be anything) in cell G10. It would then check for any blank cells in columns A and B. Once found, it would merge & center, bold the text, increase the font size, and align right. Is this only possible with a macro? I've been unable to find any formulas that could accomplish this. https://preview.redd.it/853mpr4jspog1.png?width=788&format=png&auto=webp&s=943a34a57f1d7c7f88256dd89b81b9c6fc301e34 https://preview.redd.it/e3w6ms4jspog1.png?width=453&format=png&auto=webp&s=9b189fa77d6638b627f8676dad60bc148753617c https://preview.redd.it/msh6ys4jspog1.png?width=411&format=png&auto=webp&s=d5f5025b315f24fd5f3a64c6e07e019abd3a77ab https://preview.redd.it/7j66tt4jspog1.png?width=936&format=png&auto=webp&s=1faa4df12ee74892f5fa9dc27ac95621280cf3c5 submitted by /u/Extension_Train9093 [link] [comments]