This really is off the grid 💪🏼

Great thanks, in example 7 Is it also possible to look up the newest date with an xlookup function regardless of the order of the file?

I love your videos, Mark. Thank you so much for your work! Re. the index function, what I find a simpler alternative (although still not efficient, of course) is INDEX(SORT([Name]),ROW()-ROW(Data[[#Headers],[Sorted 2]])) or INDEX(SORT([Name]),ROW()-ROW(Data[#Headers])) - although the latter throws an inconsistent formula error. Have a great Christmas break! 🙂

Thanks so much. This was very helpful and exactly what I was looking for.

I had to squeeze myself tightly into the chair. Spectacular solution. Thanks for this genius, Mark.

I think as soon as you add a custom lambda function in workbook, it got added in the workbook. Even if you copy it from another workbook and paste it in new workbook it will add required names. Am I missing something.

what is the CHOOSECOLS?

*proceeds to make hundreds of LAMBDA() functions as named ranges, copy into a ton of workbooks, and be unsung hero at work* /dream

You are the architect of spilled-arrays. I love the logic used. Thanks Mark!!

Thanks Mark The new workbook need to be saved as xlsm or can be saved as xlsx ?

How can I lookup partial matches? I know I could use Fuzzy merge but I've never really trusted it.

hey, thks! great idea, didn't think of this way to distrubute custom functions! however, I prefer a personal addin 😉

The challenge is when there are duplicate items in lookup array and still want to return unique numbers as a result. Usually it returns the first number for all the duplicate item names. Any way out here?

Something not working for me with the replies to comments: so, with Mark's permission, here is the link to my blog page on creating a Fan Chart with Python in Excel. duncanwil.blogspot.com/p/fan-chart-python-in-excel.html

Hello Mr. How are you? I want create a trial balance by using PIVOTBY or GROUPBY function with a calculated field for Balance (Debit-Credit). Need your help

Thank you very much, Mark, for the useful and valuable content you are always providing to your followers 👍...

Well done, thank you!

holy smoke! Mark Proctor for President 🔥!

It's one of the easiest and most useful excel tips I've seen, however is there any way this can be real-time? I can see it only shows the data that was updated previously while using the link.

Thank you, Mark. This is a powerful addition to Excel and a great Christmas present for Excel users. Thanks again. Have a wonderful, peaceful Christmas.😀

Excellent idea to use and manage Lambda functions. I was wondering, whether the same can be achieved using Office Scripts rather than VBA. There are several advantage using Off scripts e.g can be shared with colleagues, can work on excel online and also no need to save file in xlsm format.

It's worth noting that many people would like to pursue this for work they do on their Company Issued PC's... These days many companies are blocking the ability to create and store a Personal Macro Workbook under the guise of security. I know this, because my company did it. A work around is to save your personal Macros as an Excel Add-in and load it. That way you can still create one.

Very good Mark 👌

I mean... you could build your own... or you could just use the one that is already built-in to Monkey Tools... (for free): kzbin.info/www/bejne/imbHYayLgM6osLc :)

Mark you are some kind of wizard

Hi Mark, great video/idea as always! Was wondering though, what about saving a workbook (with the custom functions) as a template within XLSTART then rolling it out to colleagues to do the same? Cheers, Oliver

Is there any way to combine DDL and DDLSorter into the data validation so then there would be no need for the intermediate table?

This is brilliant! I use my Personal workbook for VBA code I use often but hadn’t thought of this kind of usage..very clever

An interesting method. Thank you for sharing it, Mark.

Great tricks 👍👍. I think for newbie -- > intermediate, the long codes maybe overwhelming. Personally, I try to balance stealing codes with readability; like months from now :) (especially when you're unaware source data has been changed, or maybe use #metadata to reference a step somewhere else if possible )

great video!

First class approach Mark, thank you for sharing!

Brilliant

Thanks but i dont want to share my macros with someone else and also i dont prefer to use macro for my own too because many times i have to share with collegaus

Great video, and a clever way of handling these custom functions. I always have problem with my Personal Macro, it seems to want to open whenever I view an Excel spreadsheet in the Windows Explorer.

Not particularly useful. Many of these small problems are related to your methodology of using Excel, meaning you are always chasing and correcting problems, which can become extremely complicated. It's more precise and time-saving to type your formula in the cell with specific pathways and limits, rather than your more traditional method of using ready-made, in-place solutions that are actually limited. The references to "ghost" workbook links with your methodology would eventually become uncontrollable in a large program, as you would inevitably miss some apparent errors as the program complexity increased. These are low-level, beinner solutions that will eventually become too much for the average user.

OK, but if you integrate the FLOOR or CEILING command, this needlessly long result would be eliminated.

Outstanding video Mark!!

Outstanding! I have intended to develop this capability for quite some time, but couldn’t wrap my heads around how to do it. Your approach is clever. Thank you.

Very smart move !! Exactly the infrastructure that cm lambdas need to become more popular and more tempting to use. Fantastic job !!!✌🏆

That makes it a lot easier to transfer custom functions between workbooks.😀 🙏❤️

👍👍

I do not really see the point. In my XLOOKUP, it returns the number of columns I choose in return matrix?

Do these features work well in Excel 2016

Good to know as proof of concept but it looks insanely complex. More limiting for my use case: it only allows for two sets of node pillars, not 3 or 4 or 5, etc.

I've never gotten that fancy with Excel functions before; this was cool!

Bro your content is not for ordinary audience. Your content is of pro+ level quality and for geeks. Your whole work is incredible. Stay blessed.

Outstanding illustrations with sheer clarity. Bravo 👏 Mark. Please also start a series on Python. Would love to watch your content with same clarity and detail.

Thanks a lot.

★ Get the example file ★ Sign up for our free Insiders program and get the example file: exceloffthegrid.com/insiders File Reference for this video: 0242 Function Library.zip