[leobg]

Thanks for sharing.

I often have this problem in Google Sheets. So far, i’ve resorted to copying the formula into Sublime Text and then splitting it into multiple lines. Your editor looks much more intuitive.

As demo, it would be nice if one could press a button to load some real data abd formulas. I’m reading HN on my phone. I guess many people do. Coming up with a formula and example data is an extra hurdle, and doing it using just a thumb and dumb autocorrect doubly so.

I did see the screenshots on GitHub. Thumbs up for those!

[tcho]

In case this is helpful, you can get newlines within the Excel cell itself by doing the following.

> 1. You can drag down the bottom of the formula bar/field and make it multi-line.

> 2. You can insert arbitrary newlines in an Excel formula.

> For example:

  =INDEX(
  $C$17:$S$24,
  MATCH(A6,$A$17:$A$24,0),
  MATCH(C6,$C$15:$S$15,0)
  )

I learned this from this comment from last week: https://news.ycombinator.com/item?id=46341227

[knollimar]

The "let" function may be of interest to those wanting to excel more programmatically. There's also lambda that is interesting for the more modern excel use cases.

=Let(table,$C$17:$S$24,

rowName,A6,

colName,C6,

headerRow,$C$15:$S$15,

headerCol,$A$17:$A$24,

rowIndex,MATCH(rowName,headerCol,0),

colIndex,MATCH(colName,headerRow,0),

index(table,rowIndex,colIndex)

)

or even

=LAMBDA(table,rowNames,colNames,rowToFind,colToFind,

     LET(

          rowIndex,MATCH(rowToFind,rowNames,0),

          colIndex,MATCH(colToFind,colNames,0),

          INDEX(table,rowIndex,colIndex)

     )

)($C$17:$S$24,$A$17:$A$24,$C$15:$S$15,A6,C6)

(Also alt+enter to input the newlines)

[Cordiali]

You can also put the lambda function inside the let function, which is handy.

Also, almost everyone should be using tables instead of ranges. The references are missing a few features, but it makes formulas a brazillion times more readable.

[croisillon]

you might enjoy "You Suck at Excel, by Joel Spolsky" https://www.youtube.com/watch?v=JxBg4sMusIg

[paulmooreparks]

One of the best videos ever. This set me on the path to having respectable Excel kung fu that was the envy of all my colleagues.

[bthallplz]

I haven't been able to try out the OP's link yet (I'm also on mobile right now), but for your current usage of splitting formulas across lines, I've used this tool a bunch to do that for me: https://www.excelformulabeautifier.com