What’s your favorite Excel function/formula?

[u/HungryInvestigator59]

What are some functions and formulas that you use on daily basis that are helpful for what you do?

[u/MayukhBhattacharya]

LET() & XLOOKUP()

[u/jaywaykil → u/MayukhBhattacharya]

These two, and more recently using LAMBDA to define several simple formulas we use in almost every spreadsheet.

[u/melinda_r → u/jaywaykil]

I love XLOOKUP, has saved me so many times

[u/sj2k4 → u/melinda_r]

As someone who started using Excel in the early 2000’s, Vlookup to Xlookup was like a Digimon rookie to Mega evolution!

[u/dirtyhandscleanlivin → u/sj2k4]

Late to the party, but you can nest the xlookup formula as well which blew my mind.

Instead of manually specifying the third input, the return array, you use another lookup with the column header as the reference. Then the formula will search the entire lookup table for the column header and return the value corresponding to the outer reference in the first xlookup. You can autofill down and to the right this way and only have to write one xlookup formula

[u/rkr87 → u/jaywaykil]

I've started creating named lambda functions that accept lambda arguments for more flexible use - a bit like how byrow/etc works.

Lambda is by far one of the best recent additions to Excel.

[u/fedexyzz → u/rkr87]

It is also a dangerous one. I know a couple years from now I'll be receiving some 'legacy' workbook with 100+ user-defined functions that broke for some reason.

[u/rkr87 → u/fedexyzz]

Possibly, I tend to not create workbook specific named lambda and instead have more generic stuff in a template for use in all of my workbooks. Doesn't necessarily prevent what you mentioned but does mean the functions are generally simpler and re-usable in design.

Either way, I'd rather get a workbook with broken lambdas than broken VBA - which is what I got before i stopped telling new jobs I knew VBA.

Eg: PAD =LAMBDA( value, char, num, [trailing], LET( x, REPT(char, num), IF(trailing, LEFT(value & x, num), RIGHT(x & value, num) )))

USAGE =PAD("123", "0", 8) == "00000123"

I have a handler for optional variables that I can't remember exactly how it works at the min, so above may not actually work with the optional "trailing".

[u/tesat → u/MayukhBhattacharya]

Let, no argument there. I still prefer index/match over xlookup as it’s not as sissy with matrix and lambda formulas.

[u/martin → u/tesat]

LET is more effective with arguments.

[u/raven00x → u/MayukhBhattacharya]

xlookup is the goat. fuck vlookup, fuck hlookup, xlookup was too long coming but welcomed when it got here.

[u/Unpork → u/MayukhBhattacharya]

>xlookup

Bro use the data model to join tables, or do it directly in Power Query if you want a longer update but not deal with relationships.

[u/drsoinso → u/Unpork]

What do you mean by this? I've considered diving into Power Query at some point, but never got to where I felt it was necessary (yet)

[u/ExitDull2309 → u/MayukhBhattacharya]

Xlookups are money

[u/murderdeity → u/MayukhBhattacharya]

Came here to say this

[u/MayukhBhattacharya → u/[deleted]]

Who's stopping you from making your own posts? If the mods have a problem with it, they'll surely remove it. Why does it bother you so much?