6 ms·
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 a
by tcho 9mo ago
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 https://news.ycombinator.com/item?id=46341227
- knollimar 9mo agoThe "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 9mo agoYou 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 9mo agoyou might enjoy "You Suck at Excel, by Joel Spolsky" https://www.youtube.com/watch?v=JxBg4sMusIg https://www.youtube.com/watch?v=JxBg4sMusIg
- paulmooreparks 9mo agoOne 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.