XLOOKUP is a better VLOOKUP. It was added in the last few years. Other really good new ones:
- LET for defining temporary variables inside a formula.
- LAMBDA for defining new functions
- IFS is like if-elif-else with a flat structure, solves the deeply nested IF problem
- SWITCH does what you think
- TEXTJOIN join a list of strings on a delimiter
Then there's the whole spill-arrays feature that completely changes the game. Much better than the old dynamic array formulae. You can finally treat ranges kind of as if they're dynamic-length arrays in a conventional programming language. There's MAP, FILTER, REDUCE, UNIQUE, SORTBY, HSTACK/VSTACK, etc.
Other than INDEX, please don't, for the very reasons you say. They're like eval. When someone hands me a spreadsheet that heavily uses INDIRECT I have to spend a long time figuring out what's happening. They're also volatile, meaning they're recalculated any time you do anything, rather than when they're needed, because Excel can't statically determine their cell dependencies.
Other important features: tables (i.e. the structured-reference tables, not pivot tables), Powerquery and its associated M language, VBA if you have to deal with a lot of legacy documents.
XLOOKUP is a better VLOOKUP. It was added in the last few years. Other really good new ones:
- LET for defining temporary variables inside a formula.
- LAMBDA for defining new functions
- IFS is like if-elif-else with a flat structure, solves the deeply nested IF problem
- SWITCH does what you think
- TEXTJOIN join a list of strings on a delimiter
Then there's the whole spill-arrays feature that completely changes the game. Much better than the old dynamic array formulae. You can finally treat ranges kind of as if they're dynamic-length arrays in a conventional programming language. There's MAP, FILTER, REDUCE, UNIQUE, SORTBY, HSTACK/VSTACK, etc.
There's a full list of every function here: https://support.microsoft.com/en-us/office/excel-functions-a... scroll down it and look for the ones marked with new Excel versions to see what else is new.
>4) Index, Indirect and Address
Other than INDEX, please don't, for the very reasons you say. They're like eval. When someone hands me a spreadsheet that heavily uses INDIRECT I have to spend a long time figuring out what's happening. They're also volatile, meaning they're recalculated any time you do anything, rather than when they're needed, because Excel can't statically determine their cell dependencies.
Other important features: tables (i.e. the structured-reference tables, not pivot tables), Powerquery and its associated M language, VBA if you have to deal with a lot of legacy documents.