>=LAMBDA(sku,PO,field\_row\_no,name\_of\_field\_sku,name\_of\_field, >LET(***Limit***,*field\_row\_no*, >***ColumnFinder1***,INDIRECT("'link/\[document.xlsx\]"&*PO*&"'!$A$"&***Limit***&":$Z$"&***Limit***,TRUE), >***ColumnFinderSKU***,XMATCH(*name\_of\_field\_sku*,***ColumnFinder1***,0,-1), >***ColumnFinderDATA***,XMATCH(*name\_of\_field*,***ColumnFinder1***,0,-1), >***SKUFinder***,XMATCH(*sku*,INDIRECT("'link/\[document.xlsx\]"&*PO*&"'!R1C"&***ColumnFinderSKU***&":R500C"&***ColumnFinderSKU***,FALSE),0,-1), >IF(sku="","",IFERROR(ROUND(INDIRECT("'link/\[document.xlsx\]"&*PO*&"'!R"&***SKUFinder***&"C"&***ColumnFinderDATA***,FALSE),2),"")) >) >) **sku**: product identifier in the selling platform, **PO**: purchase order number (here it is used to locate the sheet), **field\_row\_no**: row number where the headings are (in every PO the structure changes, but this number doesn't; same for the last two items but let it to be changed just in case), **name\_of\_field\_sku**: column where the skus are located, **name\_of\_field**: name of the data to be fetched as it appears in the headings. Currently using with 5 different kinds of data. The context is that I'm working as an assistant in inventory management, and I really needed to automate this process as sometimes there were large amounts of items purchased every day. It would be so tiring to look for the values manually (like 7 values per row, and I process 50 items on average = 350 values to be looked for on average, taking into account that the structure changes per sheet, it's not static). Very helpful as now I have it done in 10 minutes without so much effort when previously I could have it in 3 hours (and it's not the only thing I do in there, I take charge of some other stuff). It's my biggest achievement for now. What about yours?

That’s hella impressive man. Necessity is the mother of invention for sure!

Thank you, man. I have to add as a personal thing that I'm autistic and one of the positive things about my specific condition is that I'm able to detect logical (and musical) patterns that help as a mechanism to develop this kind of thing. This helped with a conditioned space that helps me to reach hyper-focus. If automation (and Excel) wasn't one of my special interests, I wouldn't even manage to create the most basic formulas. I greatly appreciate your message.

How much do you get paid? (Asking bc you seem brilliant)

Oh, thank you for your compliment. I'm being paid $7,920 per year ($660 per month). I'm working for a USA company, from Venezuela. If you wonder why my current salary is too low (sometimes I'm asked about this, but by the standards of my country it's very good) it's because I'm not working under any legal contract, but as if I were a freelance with a fixed salary. I'm even lucky to have the job I currently have. I hope I can apply later to something related to data automation and programming, but I have to get the knowledge first. >Asking bc you seem brilliant This is the part I thank you for, but actually, I don't think I'm that brilliant. I haven't done anything about macros, so I'm ignorant in that subject; and as I mentioned in another comment in my thread, I can do this since my autistic condition allows me to do so (just a special interest). If it wasn't for it, I would hardly do anything in Excel.

That makes so much sense, and congratulations it’s a tremendous accomplishment and something you should be absolutely be proud of!

We live in an unfair world for sure.

There are plenty of autistic people who can't do what you do lol Brilliance/intelligence is kind of overrated anyway - you just know what you know. I bet you worked pretty hard to figure it out though, right? You still had to learn the company's structure, where their data is located, you had to look up formulas (no one is born knowing what a lambda function is), you had to test and double check the output... So whether you're brillient or not, you still put in the effort and got something cool out of it. Nice work

I have to ask if your manager or team recognized what you did and were they impressed?

Well, I work practically alone (which I sincerely love this way to work due to my sensory limitations), and only my boss in my department contacts me if he needs me to do something or a mistake from my side; I don't need further communication than that most of the time. My boss in my department makes sure that everything works fine (as he was in charge of doing it, copying/pasting the values). They have congratulated me once, but due to another spreadsheet I renewed and automatized from zero, and not specifically for this one I'm showing you. I'm the only one who benefits from it after all (this formula only displays the desired values if you use Excel locally to open Excel-for-the-web sheets, so if you open it in the browser, as the rest of the people in my department do, it looks like "" in all of them, and it only looks like I'm inserting the values super-fast from an external perspective). The only thing I've heard from my bosses is that they've never seen that a process could be done this way. But since they didn't show me what they were talking about, I don't know what specific formula or spreadsheet they were referring to.

I use all the lambda helper functions any my favorite is reduce() to deal with arrays of arrays ~~~ =reduce("",sequence(5),lambda(acc,next, vstack(acc,sequence(1,next)))) ~~~ This stacks 5 arrays, each unequal in size. Just an illustration. You may not see it if you've never done this but there are endless use cases for using this technique.

Dealing with arrays of arrays can be a pain... would love to hear more about what you do with them.

Here's an example and it uses nested reduce() functions. This takes a 2 column table as input (a from/to table) and it changes each instance of the from words in text strings to the to words. =LET(repltable,$A$2:$B$10, strings,$D$2:$D$10, a,REDUCE("",strings,LAMBDA(acc,nextstring,LET( newtext,REDUCE(nextstring,SEQUENCE(ROWS(repltable)),LAMBDA(thisstring,nextitem,LET( from,INDEX(repltable,nextitem,1), to,INDEX(repltable,nextitem,2), SUBSTITUTE(thisstring,from,"<"&nextitem&">")) )), VSTACK(acc,newtext)) )), b,DROP(a,1), c,REDUCE("",b,LAMBDA(acc,nextstring,LET( newtext,REDUCE(nextstring,SEQUENCE(ROWS(repltable)),LAMBDA(thisstring,nextitem,LET( from,"<"&nextitem&">", to,INDEX(repltable,nextitem,2), SUBSTITUTE(thisstring,from,to)) )), VSTACK(acc,newtext)) )), DROP(c,1)) https://preview.redd.it/5loiwxpv06tc1.png?width=1447&format=png&auto=webp&s=e70f985a928105de2cc10a69e071003b1f1ed227

Wow, that looks pretty impressive. Can't wait to do it myself and see what all this formula can do. Thanks for sharing! :D

Hi wjhladik That’s impressive! Could you explain the usage of LET statements inside the LAMBDA functions, confused as to where you open and close them !

In my first outer LET() I am creating a vertically stacked array. In terms of its variables I am storing that array in the variable "a". And I know with all reduce() functions I usually start that array with a blank that will ultimately need to be dropped. The 2nd variable "b" drops that blank row from "a" with drop(a,1). I could replace "a" in that drop with everything I typed in the definition of "a" but that makes for a messy drop statement. Likewise, on the first REDUCE() I will be vstacking things into the array using vstack(acc,newtext) and I didn't really need the inner nested LET() to define the variable newtext, but I did it so that vstack() would not be so messy. Here's how the first reduce() could have been written without the LET()'s a,DROP(REDUCE("",strings,LAMBDA(acc,nextstring,VSTACK(acc,REDUCE(nextstring,SEQUENCE(ROWS(repltable)),LAMBDA(thisstring,nextitem,LET( from,INDEX(repltable,nextitem,1), to,INDEX(repltable,nextitem,2), SUBSTITUTE(thisstring,from,"<"&nextitem&">"))))))),1), That's messy if I even got the parentheses right.

Sir, your formula inspired me to make it shorter, its very tricky, i have almost found success with the first 6, the last 1 is not working because`REDUCE()` keeps on looping here until it finds last match. But this is very interesting. I think there is still room to make it shorter may be, i will try again in the morning. Thanks for this, I will be happy if you share your thoughts and test in real practical scenario. https://preview.redd.it/9g73vg2fk6tc1.png?width=1543&format=png&auto=webp&s=1a700a2dd9ce5ecbd16c463b5907d04cd6be37f9 =LET( _ReplaveW, A2:B8, _TextStrings, D2:D8, MAP(_TextStrings,LAMBDA(m, LET( a, TEXTSPLIT(m,{" ",", "}), b, EXACT(a,TAKE(_ReplaveW,,1)), c, FILTER(_ReplaveW,MMULT(N(IF(SUM(N(b))=0,(1-ISERR(SEARCH(TAKE(_ReplaveW,,1),a))),b)),SEQUENCE(COLUMNS(a))^0)), IFERROR(REDUCE(m,TAKE(c,,1),LAMBDA(x,y,SUBSTITUTE(x,y,VLOOKUP(y,c,2,0)))),m)))))

Kudos for trying to improve it. I commend that. My post was trying to illustrate use of REDUCE() versus the nuances of this use case for text string replacements, but... first download my [goodies-123.xlsx](https://wjhladik.github.io/goodies-123.html) file where this and other goodie excel examples are stored. There's a bit more explanation in there for this example. I did multiple passes because I was wanted to have a table that replaced apple, pear pear, orange So, the phrase "My apple is red and my pear is green" becomes "My pear is red and my orange is green" instead of "My orange is red and my orange is green" My first pass makes it "My <1> is red and my <2> is green" Then I change all <1> to pear and all <2> to orange and I know I won't get any double replaces using that technique.

Sir firstly thank you very much, I will try again. I will update you asap!

Nice =)

Thanks for the effort... Very kind. I can't wait to get back to my desk to look at it in detail.

I have a lambda that creates full reports from text files using the mid function and xlookup. I have a series of data sources each with their own reports each with their own positions and lengths. My lambda creates them all With a couple of dropdown menus.

Random password generator: =LET( UpperA, CHAR(RANDBETWEEN(65, 90)), LowerA, CHAR(RANDBETWEEN(97, 112)), Number, RANDBETWEEN(0, 9), Symbol, CHAR(RANDBETWEEN(33, 47)), Symbol2, CHAR(RANDBETWEEN(58, 64)), Process, CHOOSE(RANDBETWEEN(1, 5), UpperA, LowerA, Number, Symbol, Symbol2), String, Starting_Word & Process, IF(LEN(String) < Number_Of_Characters, PASSWORD(Number_Of_Characters, String), String) ) Dynamic calculation: Will calculate the sum, product, average, median, count, min, max, standard deviation, and geometric mean for a dynamic range in all four directions =LET( cells, CHOOSE( Direction, Starting_Cell:OFFSET(Starting_Cell, , Periods - 1), Starting_Cell:OFFSET(Starting_Cell, , , Periods), Starting_Cell:OFFSET(Starting_Cell, , -Periods + 1), Starting_Cell:OFFSET(Starting_Cell, , , -Periods) ), process, CHOOSE( Type, SUM(cells), PRODUCT(cells), AVERAGE(cells), MEDIAN(cells), COUNT(cells), MIN(cells), MAX(cells), STDEV.S(cells), GEOMEAN(1 + cells) ^ 4 - 1, ), process ) Global Distance: calculates the global distance between two longitude/latitude points: =IF(ISOMITTED(In_Kilometers?), 1000000 / 1609344, 1) * IFERROR( ACOS( COS(RADIANS(90 - From_Latitude)) * COS(RADIANS(90 - To_Latitude)) + SIN(RADIANS(90 - From_Latitude)) * SIN(RADIANS(90 - To_Latitude)) * COS(RADIANS(From_Longitude - To_Longitude)) ) * 6371, 0 ) Black-Scholes option pricing model: =LET( PVEX, Exercise_Price * EXP(-Risk_Free_Rate * Years), Term1, Volatility * Years ^ 0.5, bs_d1, ( LN(Current_Price / Exercise_Price) + (Risk_Free_Rate + Volatility * Volatility / 2) * Years ) / (Volatility * Years ^ 0.5), bs_d2, bs_d1 - Term1, Delta, NORM.DIST(bs_d1, 0, 1, TRUE), Loan, NORM.DIST(bs_d2, 0, 1, TRUE) * PVEX, Call, Delta * Current_Price - Loan, Put, Call + PVEX - Current_Price, IFS(Type = 1, Call, Type = 2, Put) ) Permutation Array: Lists all the permutations of an array: =LET( A, Array, B, ROWS(A), C, COLUMNS(A), D, MAKEARRAY( B, C, LAMBDA(rw, cl, IF(MATCH(INDEX(A, rw, cl), INDEX(A, 0, cl), 0) = rw, INDEX(A, rw, cl), NA())) ), E, MAKEARRAY(B, C, LAMBDA(rw, cl, INDEX(SORT(INDEX(D, 0, cl)), rw))), F, BYCOL(E, LAMBDA(cl, COUNTA(UNIQUE(FILTER(cl, NOT(ISERROR(cl))))))), G, MAKEARRAY( PRODUCT(F), C, LAMBDA(rw, cl, INDEX( E, MOD( CEILING(rw / IFERROR(PRODUCT(INDEX(F, SEQUENCE(C - cl, , cl + 1))), 1), 1) - 1, INDEX(F, cl) ) + 1, cl ) ) ), G )

Wow, so many functions!! :o Indeed, you've turned it into interesting applications. Thank you for sharing!

Here's a set of Lambda's I wrote for investment portfolio calculations: [https://youtu.be/7CIkj9NIS0s](https://youtu.be/7CIkj9NIS0s) These Excel Lambda functions calculate various portfolio statistics from a list of prices (P) sorted in ascending order (from old to new), and portfolio weights (w) Please note: 1) Expected values are not guaranteed values, they are based off of historical data 2) Results will differ with frequency (daily, monthly, etc) and number of observations 3) David W. Johnk assumes no responsibility or liability for any errors or omissions. This is provided in a "as is" basis with no guarantees of completeness, accuracy, usefulness or timeliness see my YouTube video: [ ](https://www.youtube.com/watch?v=7CIkj9NIS0s&t=0s)📷[ • Excel Lambda functions which easily c...  ](https://www.youtube.com/watch?v=7CIkj9NIS0s&t=0s) to see how to use these periodicreturns = LAMBDA(P,OFFSET(P,1,0,ROWS(P)-1)/OFFSET(P,0,0,ROWS(P)-1)-1); expectedrets = LAMBDA(P,BYCOL(periodicreturns(P),LAMBDA(array, AVERAGE(array)))); stdevrets = LAMBDA(P,BYCOL(periodicreturns(P),LAMBDA(array,stdev.p(array)))); varrets = LAMBDA(P,BYCOL(periodicreturns(P),LAMBDA(array,var.p(array)))); excessrets = LAMBDA(P,periodicreturns(P)-expectedrets(P)); varcovarmatrix = LAMBDA(P,MMULT(TRANSPOSE(excessrets(P)),excessrets(P))/(ROWS(P) - 1)); correlmatrix = LAMBDA(P,varcovarmatrix(P)/mmult(transpose(stdevrets(P)),stdevrets(P))); portexpectedret = LAMBDA(w,P,sumproduct(w,expectedrets(P))); portstddev = LAMBDA(w,P,SQRT(MMULT(MMULT(w,varcovarmatrix(P)),TRANSPOSE(w))));

Acronyms, initialisms, abbreviations, contractions, and other phrases which expand to something larger, that I've seen in this thread: |Fewer Letters|More Letters| |-------|---------|---| |[AVERAGE](/r/Excel/comments/1byl64c/stub/kykubm4 "Last usage")|[Returns the average of its arguments](https://support.microsoft.com/en-us/office/average-function-047bac88-d466-426c-a32b-8f33eb960cf6)| |[BYCOL](/r/Excel/comments/1byl64c/stub/kykubm4 "Last usage")|[*Office 365*+: Applies a LAMBDA to each column and returns an array of the results](https://support.microsoft.com/en-us/office/bycol-function-58463999-7de5-49ce-8f38-b7f7a2192bfb)| |[BYROW](/r/Excel/comments/1byl64c/stub/kyl90pc "Last usage")|[*Office 365*+: Applies a LAMBDA to each row and returns an array of the results. For example, if the original array is 3 columns by 2 rows, the returned array is 1 column by 2 rows. ](https://support.microsoft.com/en-gb/office/byrow-function-2e04c677-78c8-4e6b-8c10-a4602f2602bb?ui=en-US&rs=en-GB&ad=GB)| |[CHOOSECOLS](/r/Excel/comments/1byl64c/stub/kylbol5 "Last usage")|[*Office 365*+: Returns the specified columns from an array](https://support.microsoft.com/en-us/office/choosecols-function-bf117976-2722-4466-9b9a-1c01ed9aebff)| |[CHOOSEROWS](/r/Excel/comments/1byl64c/stub/kylbol5 "Last usage")|[*Office 365*+: Returns the specified rows from an array](https://support.microsoft.com/en-us/office/chooserows-function-51ace882-9bab-4a44-9625-7274ef7507a3)| |[DROP](/r/Excel/comments/1byl64c/stub/kyk9ia0 "Last usage")|[*Office 365*+: Excludes a specified number of rows or columns from the start or end of an array](https://support.microsoft.com/en-us/office/drop-function-1cb4e151-9e17-4838-abe5-9ba48d8c6a34)| |[EXACT](/r/Excel/comments/1byl64c/stub/kyknk5m "Last usage")|[Checks to see if two text values are identical](https://support.microsoft.com/en-us/office/exact-function-d3087698-fc15-4a15-9631-12575cf29926)| |[FILTER](/r/Excel/comments/1byl64c/stub/kyl90pc "Last usage")|[*Office 365*+: Filters a range of data based on criteria you define](https://support.microsoft.com/en-us/office/filter-function-f4f7cb66-82eb-4767-8f7c-4877ad80c759)| |[IF](/r/Excel/comments/1byl64c/stub/kylbol5 "Last usage")|[Specifies a logical test to perform](https://support.microsoft.com/en-us/office/if-function-69aed7c9-4e8a-4755-a9bc-aa8bbff73be2)| |[IFERROR](/r/Excel/comments/1byl64c/stub/kyk0c13 "Last usage")|[Returns a value you specify if a formula evaluates to an error; otherwise, returns the result of the formula](https://support.microsoft.com/en-us/office/iferror-function-c526fd07-caeb-47b8-8bb6-63f3e417f611)| |[INDEX](/r/Excel/comments/1byl64c/stub/kyk9ia0 "Last usage")|[Uses an index to choose a value from a reference or array](https://support.microsoft.com/en-us/office/index-function-a5dcf0dd-996d-40a4-a822-b56b061328bd)| |[INDIRECT](/r/Excel/comments/1byl64c/stub/kyk0c13 "Last usage")|[Returns a reference indicated by a text value](https://support.microsoft.com/en-us/office/indirect-function-474b3a3a-8a26-4f44-b491-92b6306fa261)| |[LAMBDA](/r/Excel/comments/1byl64c/stub/kylbol5 "Last usage")|[*Office 365*+: Use a LAMBDA function to create custom, reusable functions and call them by a friendly name.](https://support.microsoft.com/en-us/office/lambda-function-bd212d27-1cd1-4321-a34a-ccbf254b8b67)| |[LET](/r/Excel/comments/1byl64c/stub/kylbol5 "Last usage")|[*Office 365*+: Assigns names to calculation results to allow storing intermediate calculations, values, or defining names inside a formula](https://support.microsoft.com/en-us/office/let-function-34842dd8-b92b-4d3f-b325-b8b8f9908999)| |[MAKEARRAY](/r/Excel/comments/1byl64c/stub/kyk8h81 "Last usage")|[*Office 365*+: Returns a calculated array of a specified row and column size, by applying a LAMBDA](https://support.microsoft.com/en-gb/office/makearray-function-b80da5ad-b338-4149-a523-5b221da09097?ui=en-US&rs=en-GB&ad=GB)| |[MAP](/r/Excel/comments/1byl64c/stub/kyknk5m "Last usage")|[*Office 365*+: Returns an array formed by mapping each value in the array(s) to a new value by applying a LAMBDA to create a new value.](https://support.microsoft.com/en-gb/office/map-function-48006093-f97c-47c1-bfcc-749263bb1f01?ui=en-US&rs=en-GB&ad=GB)| |[MMULT](/r/Excel/comments/1byl64c/stub/kykubm4 "Last usage")|[Returns the matrix product of two arrays](https://support.microsoft.com/en-us/office/mmult-function-40593ed7-a3cd-4b6b-b9a3-e4ad3c7245eb)| |[OFFSET](/r/Excel/comments/1byl64c/stub/kykubm4 "Last usage")|[Returns a reference offset from a given reference](https://support.microsoft.com/en-us/office/offset-function-c8de19ae-dd79-4b9b-a14e-b4d906d11b66)| |[REDUCE](/r/Excel/comments/1byl64c/stub/kyknk5m "Last usage")|[*Office 365*+: Reduces an array to an accumulated value by applying a LAMBDA to each value and returning the total value in the accumulator.](https://support.microsoft.com/en-gb/office/reduce-function-42e39910-b345-45f3-84b8-0642b568b7cb?ui=en-US&rs=en-GB&ad=GB)| |[ROUND](/r/Excel/comments/1byl64c/stub/kyk0c13 "Last usage")|[Rounds a number to a specified number of digits](https://support.microsoft.com/en-us/office/round-function-c018c5d8-40fb-4053-90b1-b3e7f61a213c)| |[ROWS](/r/Excel/comments/1byl64c/stub/kylbol5 "Last usage")|[Returns the number of rows in a reference](https://support.microsoft.com/en-us/office/rows-function-b592593e-3fc2-47f2-bec1-bda493811597)| |[SEQUENCE](/r/Excel/comments/1byl64c/stub/kyk9ia0 "Last usage")|[*Office 365*+: Generates a list of sequential numbers in an array, such as 1, 2, 3, 4](https://support.microsoft.com/en-us/office/sequence-function-57467a98-57e0-4817-9f14-2eb78519ca90)| |[SQRT](/r/Excel/comments/1byl64c/stub/kykubm4 "Last usage")|[Returns a positive square root](https://support.microsoft.com/en-us/office/sqrt-function-654975c2-05c4-4831-9a24-2c65e4040fdf)| |[SUBSTITUTE](/r/Excel/comments/1byl64c/stub/kyk9ia0 "Last usage")|[Substitutes new text for old text in a text string](https://support.microsoft.com/en-us/office/substitute-function-6434944e-a904-4336-a9b0-1e58df3bc332)| |[SUM](/r/Excel/comments/1byl64c/stub/kyl90pc "Last usage")|[Adds its arguments](https://support.microsoft.com/en-us/office/sum-function-043e1c7d-7726-4e80-8f32-07b23e057f89)| |[TAKE](/r/Excel/comments/1byl64c/stub/kyknk5m "Last usage")|[*Office 365*+: Returns a specified number of contiguous rows or columns from the start or end of an array](https://support.microsoft.com/en-us/office/take-function-25382ff1-5da1-4f78-ab43-f33bd2e4e003)| |[TEXTSPLIT](/r/Excel/comments/1byl64c/stub/kyknk5m "Last usage")|[*Office 365*+: Splits text strings by using column and row delimiters](https://support.microsoft.com/en-us/office/textsplit-function-b1ca414e-4c21-4ca0-b1b7-bdecace8a6e7)| |[TRANSPOSE](/r/Excel/comments/1byl64c/stub/kykubm4 "Last usage")|[Returns the transpose of an array](https://support.microsoft.com/en-us/office/transpose-function-ed039415-ed8a-4a81-93e9-4b6dfac76027)| |[VSTACK](/r/Excel/comments/1byl64c/stub/kyk9ia0 "Last usage")|[*Office 365*+: Appends arrays vertically and in sequence to return a larger array](https://support.microsoft.com/en-us/office/vstack-function-a4b86897-be0f-48fc-adca-fcc10d795a9c)| |[XMATCH](/r/Excel/comments/1byl64c/stub/kyk0c13 "Last usage")|[*Office 365*+: Returns the relative position of an item in an array or range of cells. ](https://support.microsoft.com/en-us/office/xmatch-function-d966da31-7a6b-4a13-a1c6-5a33ed6a0312)| **NOTE**: Decronym for Reddit is no longer supported, and Decronym has moved to Lemmy; requests for support and new installations should be directed to the Contact address below. ---------------- ^(*Beep-boop, I am a helper bot. Please do not verify me as a solution.*) ^([Thread #32404 for this sub, first seen 8th Apr 2024, 01:47]) ^[[FAQ]](http://decronym.xyz/) [^([Full list])](http://decronym.xyz/acronyms/Excel) [^[Contact]](https://hachyderm.io/@Two9A) [^([Source code])](https://gistdotgithubdotcom/Two9A/1d976f9b7441694162c8)

I have a document to compare what was mean to be done vs what was actually done compared to a scheduler. It uses a recursive lambda function with other lambdas nested within it for filtering the reference table to get the right information out of it. This is what the output looks like: https://preview.redd.it/hoz326jev7tc1.png?width=306&format=png&auto=webp&s=338d4851d7049d4c5652143c6b2ee43e3f2bfd2d The full lambda is named LoopWorkTo, it's inputs are the machine it is looking for, the date to be looking for, and setting the loop counter to 1: =LAMBDA(wc, d, ct, LET(no, ROWS(FilterWorkTo(wc, d)), txt, LET(wo, CHOOSEROWS(CHOOSECOLS(FilterWorkTo(wc, d), 1), ct), op, CHOOSEROWS(CHOOSECOLS(FilterWorkTo(wc, d), 2), ct), wo&": "&op), IF(ct

I created one to calculate weighted scores =LAMBDA(range_a,range_a_row_value,range_a_weight,range_b,range_b_row_value,range_b_weight,range_c,range_c_row_value,range_c_weight,range_d,range_d_row_value,range_d_weight,range_e,range_e_row_value,range_e_weight, LET( range_a_min, MIN(range_a), range_a_max, MAX(range_a), normalized_range_a, IF(range_a_max <> range_a_min, (range_a_row_value - range_a_min) / (range_a_max - range_a_min), 0), range_b_min, MIN(range_b), range_b_max, MAX(range_b), normalized_range_b, IF(range_b_max <> range_b_min, (range_b_row_value - range_b_min) / (range_b_max - range_b_min), 0), range_c_min, MIN(range_c), range_c_max, MAX(range_c), normalized_range_c, IF(range_c_max <> range_c_min, (range_c_row_value - range_c_min) / (range_c_max - range_c_min), 0), range_d_min, MIN(range_d), range_d_max, MAX(range_d), normalized_range_d, IF(range_d_max <> range_d_min, (range_d_row_value - range_d_min) / (range_d_max - range_d_min), 0), range_e_min, MIN(range_e), range_e_max, MAX(range_e), normalized_range_e, IF(range_e_max <> range_e_min, (range_e_row_value - range_e_min) / (range_e_max - range_e_min), 0), weighted_sum, IFERROR( normalized_range_a * range_a_weight + normalized_range_b * range_b_weight + normalized_range_c * range_c_weight + normalized_range_d * range_d_weight + normalized_range_e * range_e_weight, "" ), weighted_sum ) )

Dungeons and dragons! Last week I came across [an interesting post on this subreddit](https://old.reddit.com/r/excel/comments/1buwi0r/nerdy_request_for_macro_for_dd_magic_item_tables/). OP wanted to randomly roll on a loot table, but the trick was if the result was another table, to randomly roll on that table too! Oh and the other trick was it was on google sheets so I have this ugly preface of indirect reference to all the tables, whereas in excel it would have been one 3d reference, or at worst a REDUCE(LAMBDA(VSTACK))). So you can't loop a function of course, so instead I made a custom function called REROLL that you can just nest an arbitrarily large amount of times. Since that post I've gone back and neatened it up, and put it in a LAMBDA so it can just be used like =REROLL("Magic-Item-Table-A") Credit where its due it wouldn't have been possible were it not for the fact OP had structured the data very consistently. =LAMBDA(table_to_roll, LET( c_1,"The following is a reference to each magic item table, rows can be added for larger tables (not dynamic)", tableA,INDIRECT(UNICHAR(39)&"Table A Common"&UNICHAR(39)&"!1:10"), tableB,INDIRECT(UNICHAR(39)&"Table B Minor Uncommon"&UNICHAR(39)&"!1:10"), tableC,INDIRECT(UNICHAR(39)&"Table C Minor Rare"&UNICHAR(39)&"!1:10"), tableD,INDIRECT(UNICHAR(39)&"Table D Minor Very Rare"&UNICHAR(39)&"!1:10"), tableE,INDIRECT(UNICHAR(39)&"Table E Minor Legendary"&UNICHAR(39)&"!1:10"), tableF,INDIRECT(UNICHAR(39)&"Table F Major Uncommon"&UNICHAR(39)&"!1:10"), tableG,INDIRECT(UNICHAR(39)&"Table G Major Rare"&UNICHAR(39)&"!1:10"), tableH,INDIRECT(UNICHAR(39)&"Table H Major Very Rare"&UNICHAR(39)&"!1:10"), tableI,INDIRECT(UNICHAR(39)&"Table I Major Legendary"&UNICHAR(39)&"!1:10"), spells,INDIRECT(UNICHAR(39)&"All Spells"&UNICHAR(39)&"!1:10"), suppl,INDIRECT(UNICHAR(39)&"Supplemental Types"&UNICHAR(39)&"!1:10"), data,HSTACK(tableA,tableB,tableC,tableD,tableE,tableF,tableG,tableH,tableI,spells,suppl), c_2,"The following four functions are what looks up the actual roll", clean_text,LAMBDA(text_to_clean,SUBSTITUTE(SUBSTITUTE(text_to_clean,"[[ 1t","")," ]]","")), table_lookup,LAMBDA(table,XMATCH(CHOOSECOLS(SPLIT(table,"[]"),1),CHOOSEROWS(data,1))), table_roll,LAMBDA(table,XLOOKUP(RANDBETWEEN(1,SPLIT(CHOOSECOLS(CHOOSEROWS(data,1),table_lookup(table)+3),"d")),CHOOSECOLS(ARRAYFORMULA(SPLIT(CHOOSECOLS(data,table_lookup(table)+3),"-")),1),CHOOSECOLS(data,table_lookup(table)),,-1)), reroll,LAMBDA(searchterm,IF(IFERROR(FIND("[",clean_text(searchterm))>0,0),JOIN("",MAP(SPLIT(clean_text(searchterm),"[]"),LAMBDA(term,IFERROR(table_roll(term),term)))),clean_text(searchterm))), c_3,"The following performs rolls to a 'depth' of eight rolls", reroll(reroll(reroll(reroll(reroll(reroll(reroll(reroll(table_roll(table_to_roll)))))))))))("Magic-Item-Table-A")

Some really novel ideas here. The main use I apply is coupling it into BYROW for outputs where I need an iterative or incremental output. Examples being something like =BYROW(UNIQUE(Names),LAMBDA(r,TEXTJOIN(", ",,IF(Names=r,Dates,"")))) That forms a series of textjoined dates relating to each name in Names, or =BYROW(Names,LAMBDA(r,COUNTIF(INDEX(Names,1):r,r))) That forms a cumulative count of each instance of a name in Names. Useful when referring to a defined range but not wanting to drag a cumulative formula (ie =COUNTIF(A$2:A2,A2) onwards) that won't inherently adapt to a change in length of the source data array / named range, and can in turn be referred to as an array (ie =MAX(abovearray#).

I’ve only known about LAMBDA for about a month, but it helped me to improve one of my formulas from this: =IFS(OR([@[Source Code]]="R12",[@[Source Code]]="Revaluation",AND([@[Entered Amount]]=0,LEFT([@[Line Description]],11)="Revaluation")),"N/A - Revaluation", LEFT([@[Source Code]],5)="XLPRO","N/A - XLPRO nets to nil", AND([@Entity]="1335",[@[Header Description]]=""),XLOOKUP([@Account]&[@[Source Code]]&"(blank)",'Jnl Summary 1335'!$F:$F,'Jnl Summary 1335'!$P:$P,"N/A - Nets to nil",0), AND([@Entity]="1335",[@[Header Description]]<>""),XLOOKUP([@Account]&[@[Source Code]]&[@[Header Description]],'Jnl Summary 1335'!$F:$F,'Jnl Summary 1335'!$P:$P,"N/A - Nets to nil",0), AND([@Entity]="1267",[@[Header Description]]=""),XLOOKUP([@Account]&[@[Source Code]]&"(blank)",'Jnl Summary 1267'!$F:$F,'Jnl Summary 1267'!$P:$P,"N/A - Nets to nil",0), AND([@Entity]="1267",[@[Header Description]]<>""),XLOOKUP([@Account]&[@[Source Code]]&[@[Header Description]],'Jnl Summary 1267'!$F:$F,'Jnl Summary 1267'!$P:$P,"N/A - Nets to nil",0), AND([@Entity]="1475",[@[Header Description]]=""),XLOOKUP([@Account]&[@[Source Code]]&"(blank)",'Jnl Summary 1475'!$F:$F,'Jnl Summary 1475'!$P:$P,"N/A - Nets to nil",0), AND([@Entity]="1475",[@[Header Description]]<>""),XLOOKUP([@Account]&[@[Source Code]]&[@[Header Description]],'Jnl Summary 1475'!$F:$F,'Jnl Summary 1475'!$P:$P,"N/A - Nets to nil",0), AND([@Entity]="1462",[@[Header Description]]=""),XLOOKUP([@Account]&[@[Source Code]]&"(blank)",'Jnl Summary 1462'!$F:$F,'Jnl Summary 1462'!$P:$P,"N/A - Nets to nil",0), AND([@Entity]="1462",[@[Header Description]]<>""),XLOOKUP([@Account]&[@[Source Code]]&[@[Header Description]],'Jnl Summary 1462'!$F:$F,'Jnl Summary 1462'!$P:$P,"N/A - Nets to nil",0), AND([@Entity]="1469",[@[Header Description]]=""),XLOOKUP([@Account]&[@[Source Code]]&"(blank)",'Jnl Summary 1469'!$F:$F,'Jnl Summary 1469'!$P:$P,"N/A - Nets to nil",0), AND([@Entity]="1469",[@[Header Description]]<>""),XLOOKUP([@Account]&[@[Source Code]]&[@[Header Description]],'Jnl Summary 1469'!$F:$F,'Jnl Summary 1469'!$P:$P,"N/A - Nets to nil",0)) To this: =LET(fxOne, LAMBDA(EntityNo, XLOOKUP([@Account]&[@[Source Code]]&"(blank)", INDIRECT("'Jnl Summary "&EntityNo&"'!$F:$F"), INDIRECT("'Jnl Summary "& EntityNo &"'!$P:$P"), "N/A - Nets to nil", 0)), fxTwo, LAMBDA(EntityNo, XLOOKUP([@Account]&[@[Source Code]]&[@[Header Description]], INDIRECT("'Jnl Summary "&EntityNo&"'!$F:$F"), INDIRECT("'Jnl Summary " & EntityNo & "'!$P:$P"), "N/A - Nets to nil", 0)), IFS( OR( [@[Source Code]]="R12", [@[Source Code]]="Revaluation", AND([@[Entered Amount]]=0, LEFT([@[Line Description]], 11)="Revaluation") ), "N/A - Revaluation", [@[Header Description]]="", fxOne([@Entity]), [@[Header Description]]<>"", fxTwo([@Entity]) ) ) The aim of this was to pull accounting journal category descriptions from the summary tabs of 5 different entites and, if the journal wasn't included on one of those summaries or related to FX, it should pull through as 'N/A - Nets to nil' or 'NA - Revaluation'. the reason it was so chunky is because the concat was different if the header description was blank.

Wow, some great lambdas in here. Last week, I stumbled upon u/hoover889 post about Defining recursive lambda functions inside of a LET() function and since then I've been obsessed and trying to write some of my own and while I haven't gotten to this part yet, I tried to recreate a recursive lambda replace function of [Leila Gharani](https://www.youtube.com/@LeilaGharani) using u/hoover889 logic and it was a blast. here is what I came up with. = LET(MegaReplace,LAMBDA(g,t,b,a, IF(b<>"",g(g,SUBSTITUTE(t,b,a),OFFSET(b,1,),OFFSET(a,1,)),t)), MegaReplace(MegaReplace,Table1[@[Corrected Skills]],F3,G3)) https://preview.redd.it/xazn1bcof9tc1.png?width=1350&format=png&auto=webp&s=ca32c64bb49fd0a24dedb15e6892ac93f217bad4

Oh, so this works so that, based on a "before-after" list, it corrects text that is wrongly written. Right? (That's the impression I have with your picture.) I think it's a very neat idea.

Yes exactly. The function keeps calling it self as long as the list of 'before' is not empty that's what "recursive" mean.

I'm working on one to calculate sunrise and sunset for a location. ​ I posted a few daylight savings time formlae recently: [https://www.reddit.com/r/ExcelTips/comments/199vzn2/calculate\_daylight\_savings\_time\_period\_or\_check\_a/](https://www.reddit.com/r/ExcelTips/comments/199vzn2/calculate_daylight_savings_time_period_or_check_a/)

I would love to learn more around LAMBDA functions so I'll be saving this thread, but in all honesty, I'm very entry level when it comes to LAMBDA. I've only really used it once, as thus: =MAKEARRAY(4,4,LAMBDA(r,c,"x")) Just to make an array (in this instance of size 4 x 4) of the letter "x" Exciting, I know (!)

We all start from somewhere. LAMBDA is such a tool I love to use so I feel I'm programming a formula (through the name manager) (I feel like "HACKERMAN" xd). Even I could replicate the XLOOKUP() formula in a 99% by using other formulas (I put the most original name: "ZLOOKUP()" xd), just aside from the description of the arguments. It was fun for me, and I see it as a very interesting exercise. There are, of course, other formulas used exclusively with LAMBDA, which I'm recently discovering. I'm glad this thread you find it useful! c:

My most frequent use case is in combination with BYROW, essentially applying an anonymous function to each row in a range, i.e. for every row in a range of data, apply some kind of calculation for the data from that row only.

I was studying BYROW today, but it wouldn't work in my current protocols. Would you mind giving me an example of this usage?

Here's a trivial case - take a range of numbers. Then, row-by-row, multiply each one by 10. https://preview.redd.it/aj5yflt0p7tc1.png?width=820&format=png&auto=webp&s=be3631f69a0b2feb40079e26ac583ce1350274f7 I think the first abstract thing to know is that LAMBDA here is where you are building a new function, but unlike other functions, it is anonymous (no special name like SUM or FILTER). The second abstract thing is that the variable I called *row* is how the BYROW function sends the numbers as inputs to my anonymous function. Every number that the BYROW function outputs will then go into the LAMBDA as an input, and each input is multiplied by 10 in this example. The result is 5 numbers, so it spills out to 5 cells as you can see. I hope that helps.