Intro.
As of this writing functions TOCOL and TOROW are only available in Excel 365 and Excel online/Web. Excel 2021 supports Dynamic Arrays so simulating these 2 functions is possible
There’s a limitation though. Most Excel functions accept parameter(s), something we can’t replicate here. This means our parameters must be "encapsulated" in our LET formula or referenced on the grid/sheet
Updated workbook supporting this article is available here (inc. examples and formulas embedded as plain text files)
Updated 2024-10-18: Re-written from scratch to reduce the calcs as much as possible + changed the FILTERing so this work exactly as the 365 function when there's no more rows/columns (empty array) => #CALC! error
1 - TOCOL and TOROW parameters
Both functions accept the same parameters:
array (required)
ignore (optional) – Default = 0
scan_by_column (optional) – Default = FALSE
As we can’t write/simulate a function the parameters must be provided in the LET formula (or referenced on the grid/sheet)
2 - About Blanks
The formulas behave exactly as TOCOL/TOROW in Excel 365/Web re. Blanks. A cell is Blank when it's really empty. In other words, cells that contain formulas that return single/double-quotes or have invisible/unprintable chars. won't be filtered out when the ignore parameter is set to 1 or 3