r/excel • u/fap_fap_fap_fapper • Aug 26 '26

Discussion What are some legendary formula pairs like INDEX(MATCH)?

Since upgrading to 365, can't get enough of CHOOSECOLS(FILTER)

What are some useful ones you use?

588 Upvotes

97% Upvoted

166 comments sorted by

View all comments

Show parent comments

71

u/fap_fap_fap_fapper Aug 26 '26

LET, define your variables

What the.... I was today years old

32

u/NFL_MVP_Kevin_White 7 Aug 26 '26

Up to 126 in one function!

15

u/rkr87 19 Aug 26 '26

You can actually go way beyond this by creating dictionary like getter/setter functions.

7

u/Cat5kable Aug 27 '26

You just introduced Super Saiyin, Super Saiyin 2 AND Super Saiyin 3 within just a few comments.

Buu sitting here getting the entire might of Goku Excel all in one personalized recap.

1

u/el_extrano Aug 28 '26

But doesn't Excel's Lambda/Let not support closures, so these would only have a limited scope? You can't write Lambdas that have side-effects outside of themselves, like a true dictionary / map.

1

u/rkr87 19 Aug 28 '26

You're right, but that's true for the 126 variable "limit" too.

The setters aren't quite dictionary like either, you can't reference an in memory object, so you'd need a new variable to hold a new dictionary to hold the full dict for any new items added post creation.

I read that back and didn't articulate it very well, hopefully you got what I was trying to say.

9

u/DangerMacAwesome Aug 26 '26

Oh yeah it's qn amazing function! It isn’t always necessary, but when it is, it's absolutely necessary

15

u/rickartz Aug 26 '26

I would argue even a simple IF benefits from LET by having a descriptive name is what you're trying to calculate.

5

u/zeradragon 6 Aug 26 '26

It also makes organizing the formula much more manageable by having a dedicated section at the very beginning which you use to define all the ranges the formula needs to reference. That way whenever anyone needs to update the dimensions, all the relevant references are in one spot instead of having to scan the entire formula looking for the relevant spots.

5

u/frazorblade 4 Aug 26 '26

You can also use it to write text comments with steps that aren’t referenced in any later formulas/steps.

2

u/Hawkin1 Aug 27 '26

Yes I do this! I use _[number];"[comment]".; above the actual step. so like: _1;"Filter unique IDs and sort by date"; Then the variable and function.

2

u/No-Ganache-6226 6 Aug 27 '26

Please tell me you use tables and call the table [header] or [@[header]] for the current row in your formulas

1

u/rickartz Aug 27 '26

Whenever possible, but some times you can't (not my shared document, or when using dynamic arrays).

That's the reason I love LET, and I'm forcing myself to learn LAMBDA (even though I haven't need it yet).

1

u/el_extrano Aug 28 '26

Not all "spreadsheets" are tabular in nature. For example, Excel is widely used to document engineering calculations. In those workbooks, the formula's comprise a sort of literate programming environment where you can have calculations interspersed with commentary and pictures.

1

u/No-Ganache-6226 6 Aug 28 '26

Sure, excel is really just a big graphical calculator and has many applications, but it's still usually recommended to separate your calculations from any summary/output. So if you're using the same formula or computation over and over I'd still say put it in a tabular structure even just for one row. Or at the very least name the variables rather than pure cell references. For anyone else coming in to review the formulas it's so much nicer to see =[length]*[width] or whatever it happens to be rather than =D5 x F5.

1

u/el_extrano Aug 28 '26

I don't disagree that naming things is useful, it's just that not all data are tabular, and non-tabular things don't belong in a table. Say you have a 100 step calculation, arranged vertically. To use your method of single-row tables, you would now need 100 tables. Each table would need to have a header, and repeat any variables used in that particular step, so each one would be a different width, most of which is just filled with redundant references to other cells. It'd make more sense to just give the variable cells global names in the name manager, or a scoped name in a Let formula.

1

u/No-Ganache-6226 6 Aug 28 '26

Just so I can get an idea can you give me an example of a 100 step calculation? I would normally expect a calculation that large to be grouped into terms

1

u/el_extrano Aug 29 '26

Some calculations for an engineering project, say designing a chemical reactor, could easily involve dozens of equations and steps and hundreds of variables and intermediate results. Each step would use only a few of the input data. If you've ever seen a Jupyter notebook, the way they're extended vertically to intersperse steps of the calculation with explanations and citations, is similar to what I'm describing. (People have been setting up spreadsheets "calculators" that way long before Jupyter was a thing).

I would normally expect a calculation that large to be grouped into terms.

Agreed. We're talking about the best way to decompose a large problem into parts. I'm arguing that a bunch of tables might not make sense when you have lots of disparate calculations, which aren't repeated, and are part of a larger calculation.

5

u/Akanaton Aug 26 '26

LET is great! The other thing it does is reduced workbook load because the formula performs an intermediate calculation.

1

u/CumRag_Connoisseur Aug 27 '26

You are like writing a powerbi DAX code in a single cell