Last active
January 28, 2026 08:45
-
-
Save RackofLambda/8c38a707b1194825069bb8a780600ce4 to your computer and use it in GitHub Desktop.
A collection of Microsoft Excel LAMBDA functions for scanning tables and arrays.
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| /* List of LAMBDA functions in this collection: | |
| -------------------------------------------- | |
| LISTBY --------->> Groups all values from a table or array by distinct item and returns a jagged array of associated lists. | |
| INSTANCENUM ---->> Returns the running count of distinct items in an unordered table or array. | |
| RUNNINGTOT ----->> Returns the running total of values by distinct item in an unordered table or array. | |
| RUNNINGTOT2 ---->> Returns the running total of values by distinct item in an unordered table or array, with the option to prioritize order by date. | |
| JOINROWS ------->> Creates a key identifier row by combining the values of each row in an array on a column-by-column basis. | |
| JOINCOLS ------->> Creates a key identifier field by combining the values of each column in an array on a row-by-row basis. | |
| SCANBYROW ------>> Scans an array in row-major order by applying a LAMBDA to each value and returns an array of intermediate results for each row. | |
| SCANBYCOL ------>> Scans an array in col-major order by applying a LAMBDA to each value and returns an array of intermediate results for each column. | |
| SCAN3 ---------->> Scans up to 3 input arrays by applying a LAMBDA to each value of each array and returns a single array of intermediate results. | |
| SCAN5 ---------->> Scans up to 5 input arrays by applying a LAMBDA to each value of each array and returns a single array of intermediate results. | |
| REDUCE3 -------->> Reduces up to 3 input arrays to an accumulated value by applying a LAMBDA to each value of each array and returns the total accumulated value. | |
| REDUCE5 -------->> Reduces up to 5 input arrays to an accumulated value by applying a LAMBDA to each value of each array and returns the total accumulated value. | |
| EVALS ---------->> Returns all items from a vector of multi-value elements, or a specified item from an array of multi-value elements. | |
| EVALS_NUM ------>> (recursive) Attempts to auto-detect the number of items contained within a multi-value element. | |
| VALS2 ---------->> Stores up to 2 variables in a single element (TYPE 128), which can be recalled by id number as needed. | |
| VALS3 ---------->> Stores up to 3 variables in a single element (TYPE 128), which can be recalled by id number as needed. | |
| VALS4 ---------->> Stores up to 4 variables in a single element (TYPE 128), which can be recalled by id number as needed. | |
| VALS5 ---------->> Stores up to 5 variables in a single element (TYPE 128), which can be recalled by id number as needed. | |
| LTrimChar ------>> (recursive) Removes leading characters from a text string or array. | |
| RTrimChar ------>> (recursive) Removes trailing characters from a text string or array. | |
| TRIMCHAR ------->> Removes leading and/or trailing characters from a text string or array. | |
| BETWEEN -------->> A helper function for writing dynamic criteria to filter a table or array by values that fall within a specified range. | |
| INCLUDE -------->> A helper function for writing dynamic criteria to filter a table or array by text that equals, begins with, ends with, or contains. | |
| LIKE ----------->> A helper function for writing dynamic criteria to filter a table or range using wildcards and comparison operators. | |
| Nz ------------->> Returns a zero-length string or another specified value when a variant is null (derived from a blank cell in a range). | |
| */ | |
| /* FUNCTION NAME: LISTBY | |
| DESCRIPTION: Groups all values from a table or array by distinct item and returns a jagged array of associated lists. | |
| SYNTAX: =LISTBY(row_fields,values,[sort_order],[rotate]) | |
| ARGUMENTS: row_fields (required) - the row identifier field(s) that contain the values to group each list by. | |
| values (required) - the value field (vertical vector) that contains the data to be listed. | |
| [sort_order] (integer, optional) - specifies the sort order (default is unsorted, if omitted). | |
| 1 - ascending order | |
| -1 - descending order | |
| [rotate] (Boolean, optional) - transposes the results. | |
| 0 or FALSE - lists are returned horizontally in rows (default) | |
| 1 or TRUE - lists are returned vertically in columns | |
| SAMPLE 1: X Y Z | |
| a c 1 | |
| a d 2 | |
| b d 3 | |
| a c 4 | |
| b c 5 | |
| a d 6 | |
| a c 7 | |
| b d 8 | |
| =LISTBY(A2:B9,C2:C9) | |
| a c 1 4 7 | |
| a d 2 6 | |
| b c 5 | |
| b d 3 8 | |
| =LISTBY(A2:B9,C2:C9,-1,1) | |
| a a b b | |
| c d c d | |
| 7 6 5 8 | |
| 4 2 3 | |
| 1 | |
| SAMPLE 2: "https://www.youtube.com/watch?v=kBhRyXkqefI&t=225" | |
| =LISTBY(Data[Item],Data[Value],,TRUE) | |
| */ | |
| LISTBY = LAMBDA(row_fields,values,[sort_order],[rotate], | |
| LET( | |
| w, COLUMNS(row_fields), | |
| a, SORT( | |
| HSTACK(row_fields, values), | |
| SEQUENCE(, w + NOT(ISOMITTED(sort_order))), | |
| IF(ISOMITTED(sort_order), sort_order, HSTACK(EXPAND(1,, w, 1), sort_order)) | |
| ), | |
| b, GROUPBY(TAKE(a,, w), TAKE(a,, -1), ROWS, 0, 0), | |
| k, TAKE(b,, -1), | |
| j, SEQUENCE(, MAX(k)), | |
| i, DROP(VSTACK(0, SCAN(, k, SUM)), -1), | |
| x, HSTACK(DROP(b,, -1), IF(k >= j, INDEX(a, i + j, COLUMNS(a)), "")), | |
| IF(rotate, TRANSPOSE(x), x) | |
| ) | |
| ); | |
| /* FUNCTION NAME: INSTANCENUM | |
| DESCRIPTION: Returns the running count of distinct items in an unordered table or array. | |
| SYNTAX: =INSTANCENUM(key_fields,[horizontal],[last_to_first]) | |
| ARGUMENTS: key_fields (required) - the identifier column(s) or row(s) to be scanned. | |
| [horizontal] (Boolean, optional) - specifies the orientation of the table. | |
| 0 or FALSE - vertical (default) | |
| 1 or TRUE - horizontal | |
| [last_to_first] (Boolean, optional) - specifies the scan order. | |
| 0 or FALSE - first-to-last: scans from top-to-bottom or left-to-right (default) | |
| 1 or TRUE - last-to-first: scans from bottom-to-top or right-to-left | |
| SAMPLE 1: X Y Z =INSTANCENUM(A2:A9) =INSTANCENUM(A2:A9,,TRUE) | |
| a c 1 1 5 | |
| a d 2 2 4 | |
| b d 3 1 3 | |
| a c 4 3 3 | |
| b c 5 2 2 | |
| a d 6 4 2 | |
| a c 7 5 1 | |
| b d 8 3 1 | |
| =DROP(PIVOTBY(A2:B9,INSTANCENUM(A2:B9),C2:C9,SINGLE,0,0,,0),1) | |
| a c 1 4 7 | |
| a d 2 6 | |
| b c 5 | |
| b d 3 8 | |
| =DROP(PIVOTBY(INSTANCENUM(A2:B9,,1),A2:B9,C2:C9,SINGLE,0,0,,0),,1) | |
| a a b b | |
| c d c d | |
| 7 6 5 8 | |
| 4 2 3 | |
| 1 | |
| SAMPLE 2: X a a a b a b a a | |
| Y c d c c c d c d | |
| =INSTANCENUM(B1:I2,TRUE) | |
| 1 1 2 1 3 1 4 2 | |
| =INSTANCENUM(B1:I2,1,1) | |
| 4 2 3 1 2 1 1 1 | |
| */ | |
| INSTANCENUM = LAMBDA(key_fields,[horizontal],[last_to_first], | |
| IF( | |
| horizontal, | |
| LET( | |
| vec, IF(ROWS(key_fields) = 1, key_fields, BYCOL(key_fields, LAMBDA(x, TEXTJOIN("|", 0, x)))), | |
| arr, VSTACK(vec, SEQUENCE(, COLUMNS(vec))), | |
| srt, IF(last_to_first, SORT(arr, {1,2}, {1,-1}, 1), SORT(arr,,, 1)), | |
| key, TAKE(srt, 1), | |
| SORTBY(SCAN(0, key = DROP(HSTACK("", key),, -1), LAMBDA(a,v, 1 + a * v)), DROP(srt, 1)) | |
| ), | |
| LET( | |
| vec, IF(COLUMNS(key_fields) = 1, key_fields, BYROW(key_fields, LAMBDA(x, TEXTJOIN("|", 0, x)))), | |
| arr, HSTACK(vec, SEQUENCE(ROWS(vec))), | |
| srt, IF(last_to_first, SORT(arr, {1,2}, {1,-1}), SORT(arr)), | |
| key, TAKE(srt,, 1), | |
| SORTBY(SCAN(0, key = DROP(VSTACK("", key), -1), LAMBDA(a,v, 1 + a * v)), DROP(srt,, 1)) | |
| ) | |
| ) | |
| ); | |
| /* FUNCTION NAME: RUNNINGTOT | |
| DESCRIPTION: Returns the running total of values by distinct item in an unordered table or array. | |
| SYNTAX: =RUNNINGTOT(key_fields,values,[horizontal],[last_to_first]) | |
| ARGUMENTS: key_fields (required) - the identifier column(s) or row(s) to be scanned. | |
| values (required) - the value field to be totalled. | |
| [horizontal] (Boolean, optional) - specifies the orientation of the table. | |
| 0 or FALSE - vertical (default) | |
| 1 or TRUE - horizontal | |
| [last_to_first] (Boolean, optional) - specifies the scan order. | |
| 0 or FALSE - first-to-last: scans from top-to-bottom or left-to-right (default) | |
| 1 or TRUE - last-to-first: scans from bottom-to-top or right-to-left | |
| SAMPLE 1: X Y Z =RUNNINGTOT(A2:B9,C2:C9) =RUNNINGTOT(A2:B9,C2:C9,,TRUE) | |
| a c 8 8 24 | |
| a d 3 3 4 | |
| a c 5 13 16 | |
| b c 2 2 2 | |
| a c 4 17 11 | |
| b d 6 6 6 | |
| a c 7 24 7 | |
| a d 1 4 1 | |
| SAMPLE 2: X a a a b a b a a | |
| Y c d c c c d c d | |
| Z 8 3 5 2 4 6 7 1 | |
| =RUNNINGTOT(B1:I2,B3:I3,TRUE) | |
| 8 3 13 2 17 6 24 4 | |
| =RUNNINGTOT(B1:I2,B3:I3,1,1) | |
| 24 4 16 2 11 6 7 1 | |
| */ | |
| RUNNINGTOT = LAMBDA(key_fields,values,[horizontal],[last_to_first], | |
| IF( | |
| horizontal, | |
| LET( | |
| vec, IF(ROWS(key_fields) = 1, key_fields, BYCOL(key_fields, LAMBDA(x, TEXTJOIN("|", 0, x)))), | |
| arr, VSTACK(vec, values, SEQUENCE(, COLUMNS(vec))), | |
| srt, IF(last_to_first, SORT(arr, {1,3}, {1,-1}, 1), SORT(arr,,, 1)), | |
| key, TAKE(srt, 1), | |
| vλl, MAP(key = DROP(HSTACK("", key),, -1), CHOOSEROWS(srt, 2), LAMBDA(b,v, LAMBDA(a, v + a * b))), | |
| SORTBY(SCAN(0, vλl, LAMBDA(a,λ, λ(a))), TAKE(srt, -1)) | |
| ), | |
| LET( | |
| vec, IF(COLUMNS(key_fields) = 1, key_fields, BYROW(key_fields, LAMBDA(x, TEXTJOIN("|", 0, x)))), | |
| arr, HSTACK(vec, values, SEQUENCE(ROWS(vec))), | |
| srt, IF(last_to_first, SORT(arr, {1,3}, {1,-1}), SORT(arr)), | |
| key, TAKE(srt,, 1), | |
| vλl, MAP(key = DROP(VSTACK("", key), -1), CHOOSECOLS(srt, 2), LAMBDA(b,v, LAMBDA(a, v + a * b))), | |
| SORTBY(SCAN(0, vλl, LAMBDA(a,λ, λ(a))), TAKE(srt,, -1)) | |
| ) | |
| ) | |
| ); | |
| /* FUNCTION NAME: RUNNINGTOT2 | |
| DESCRIPTION: Returns the running total of values by distinct item in an unordered table or array, with the option to prioritize order by date. | |
| SYNTAX: =RUNNINGTOT2(key_fields,values,[date_field],[horizontal],[last_to_first]) | |
| ARGUMENTS: key_fields (required) - the identifier column(s) or row(s) to be scanned. | |
| values (required) - the value field to be totalled. | |
| [date_field] (optional) - the field to sort by (ascending order; not affected by [last-to-first]). | |
| [horizontal] (Boolean, optional) - specifies the orientation of the table. | |
| 0 or FALSE - vertical (default) | |
| 1 or TRUE - horizontal | |
| [last_to_first] (Boolean, optional) - specifies the scan order. | |
| 0 or FALSE - first-to-last: scans from top-to-bottom or left-to-right (default) | |
| 1 or TRUE - last-to-first: scans from bottom-to-top or right-to-left | |
| EXAMPLES: *Same as RUNNINGTOT shown above, but with the option to sort by date first.* | |
| */ | |
| RUNNINGTOT2 = LAMBDA(key_fields,values,[date_field],[horizontal],[last_to_first], | |
| IF( | |
| horizontal, | |
| LET( | |
| vec, IF(ROWS(key_fields) = 1, key_fields, BYCOL(key_fields, LAMBDA(x, TEXTJOIN("|", 0, x)))), | |
| cId, SEQUENCE(, COLUMNS(vec)), | |
| arr, VSTACK(vec, values, cId), | |
| srt, IF( | |
| ISOMITTED(date_field), | |
| IF(last_to_first, SORT(arr, {1,3}, {1,-1}, 1), SORT(arr,,, 1)), | |
| IF(last_to_first, SORTBY(arr, vec, 1, date_field, 1, cId, -1), SORTBY(arr, vec, 1, date_field, 1)) | |
| ), | |
| key, TAKE(srt, 1), | |
| vλl, MAP(key = DROP(HSTACK("", key),, -1), CHOOSEROWS(srt, 2), LAMBDA(b,v, LAMBDA(a, v + a * b))), | |
| SORTBY(SCAN(0, vλl, LAMBDA(a,λ, λ(a))), TAKE(srt, -1)) | |
| ), | |
| LET( | |
| vec, IF(COLUMNS(key_fields) = 1, key_fields, BYROW(key_fields, LAMBDA(x, TEXTJOIN("|", 0, x)))), | |
| rId, SEQUENCE(ROWS(vec)), | |
| arr, HSTACK(vec, values, rId), | |
| srt, IF( | |
| ISOMITTED(date_field), | |
| IF(last_to_first, SORT(arr, {1,3}, {1,-1}), SORT(arr)), | |
| IF(last_to_first, SORTBY(arr, vec, 1, date_field, 1, rId, -1), SORTBY(arr, vec, 1, date_field, 1)) | |
| ), | |
| key, TAKE(srt,, 1), | |
| vλl, MAP(key = DROP(VSTACK("", key), -1), CHOOSECOLS(srt, 2), LAMBDA(b,v, LAMBDA(a, v + a * b))), | |
| SORTBY(SCAN(0, vλl, LAMBDA(a,λ, λ(a))), TAKE(srt,, -1)) | |
| ) | |
| ) | |
| ); | |
| /* FUNCTION NAME: JOINROWS | |
| DESCRIPTION: Creates a key identifier row by combining the values of each row in an array on a column-by-column basis. | |
| SYNTAX: =JOINROWS(array,[delimiter]) | |
| ARGUMENTS: array (required) - the row(s) to be joined. | |
| [delimiter] (optional) - the character(s) inserted between each value (default is "|"). | |
| EXAMPLES: =JOINROWS(A2:H4) | |
| =JOINROWS(A2:H4,", ") | |
| =INDEX(return_arr,SEQUENCE(ROWS(return_arr)),XMATCH(JOINROWS(lookup_vals),JOINROWS(lookup_arr))) | |
| */ | |
| JOINROWS = LAMBDA(array,[delimiter], | |
| IF( | |
| ROWS(array) = 1, | |
| array, | |
| LET( | |
| k, IF(ISOMITTED(delimiter), "|", delimiter), | |
| BYCOL(array, LAMBDA(x, TEXTJOIN(k, 0, x))) | |
| ) | |
| ) | |
| ); | |
| /* FUNCTION NAME: JOINCOLS | |
| DESCRIPTION: Creates a key identifier field by combining the values of each column in an array on a row-by-row basis. | |
| SYNTAX: =JOINCOLS(array,[delimiter]) | |
| ARGUMENTS: array (required) - the column(s) to be joined. | |
| [delimiter] (optional) - the character(s) inserted between each value (default is "|"). | |
| EXAMPLES: =JOINCOLS(A2:C9,", ") | |
| =JOINCOLS(Table1[[Region]:[Department]]) | |
| =INDEX(return_arr,XMATCH(JOINCOLS(lookup_vals),JOINCOLS(lookup_arr)),SEQUENCE(,COLUMNS(return_arr))) | |
| */ | |
| JOINCOLS = LAMBDA(array,[delimiter], | |
| IF( | |
| COLUMNS(array) = 1, | |
| array, | |
| LET( | |
| k, IF(ISOMITTED(delimiter), "|", delimiter), | |
| BYROW(array, LAMBDA(r, TEXTJOIN(k, 0, r))) | |
| ) | |
| ) | |
| ); | |
| /* FUNCTION NAME: SCANBYROW | |
| DESCRIPTION: Scans an array in row-major order by applying a LAMBDA to each value and returns an array of intermediate results for each row. | |
| SYNTAX: =SCANBYROW(initial_value,array,function) | |
| ARGUMENTS: initial_value (semi-required) - the starting value for the accumulator (referenced in the first iteration of each row). | |
| array (required) - the array to be scanned. | |
| function (required) - a custom LAMBDA function with 2 parameters (accumulator, current_value) or an eta-reduced LAMBDA. | |
| REMARKS: Maintains the same high-level of efficiency with both range and array objects. | |
| EXAMPLES: =SCANBYROW(,A2:H100,LAMBDA(a,v,IF(v="",a,v))) | |
| =SCANBYROW("",A2:H100,LAMBDA(a,v,IF(v="",a,v))) | |
| =SCANBYROW(,SEQUENCE(10,4),SUM) | |
| =SCANBYROW(0,SEQUENCE(50000,10),SUM) | |
| =SCANBYROW(10,TRANSPOSE(SEQUENCE(4,10)),LAMBDA(a,v,a+2*v)) | |
| =SCANBYROW(,CHAR(SEQUENCE(5,5,65)),CONCAT) | |
| =SCANBYROW("",CHAR(SEQUENCE(5,5,65)),LAMBDA(a,v,TEXTJOIN("|",,a,v))) | |
| SAMPLE 1: "https://techcommunity.microsoft.com/discussions/excelgeneral/accumulating-arrays/3045626" | |
| =DROP(SCANBYROW(,HSTACK(initial,array),SUM),,1) | |
| =DROP(SCANBYROW(,HSTACK(invested,1+growth),PRODUCT),,1) | |
| SAMPLE 2: "https://techcommunity.microsoft.com/discussions/ExcelGeneral/using-scan-function-with-oddly-shaped-arrays/4384192" | |
| =DROP(SCANBYROW(,HSTACK(B5:B7,1+D5:F7),PRODUCT),,1)*ISNUMBER(D5:F7) | |
| =IF(ISBLANK(C11:G13),"",DROP(SCANBYROW(,HSTACK(B11:B13,1+C11:G13),PRODUCT),,1)) | |
| */ | |
| SCANBYROW = LAMBDA(initial_value,array,function, | |
| LET( | |
| rs, CHAR(30), | |
| DROP( | |
| SCAN( | |
| initial_value, | |
| EXPAND(array,, COLUMNS(array) + 1, rs), | |
| LAMBDA(a,v, IF(IFERROR(v = rs, 0), initial_value, IF(ISOMITTED(a), v, function(a, v)))) | |
| ),, | |
| -1 | |
| ) | |
| ) | |
| ); | |
| /* FUNCTION NAME: SCANBYCOL | |
| DESCRIPTION: Scans an array in col-major order by applying a LAMBDA to each value and returns an array of intermediate results for each column. | |
| SYNTAX: =SCANBYCOL(initial_value,array,function) | |
| ARGUMENTS: initial_value (semi-required) - the starting value for the accumulator (referenced in the first iteration of each column). | |
| array (required) - the array to be scanned. | |
| function (required) - a custom LAMBDA function with 2 parameters (accumulator, current_value) or an eta-reduced LAMBDA. | |
| REMARKS: Maintains the same high-level of efficiency with both range and array objects. | |
| EXAMPLES: =SCANBYCOL(,A2:H100,LAMBDA(a,v,IF(v="",a,v))) | |
| =SCANBYCOL("",A2:H100,LAMBDA(a,v,IF(v="",a,v))) | |
| =SCANBYCOL(,SEQUENCE(4,10),SUM) | |
| =SCANBYCOL(0,SEQUENCE(50,10000),SUM) | |
| =SCANBYCOL(10,TRANSPOSE(SEQUENCE(10,4)),LAMBDA(a,v,a+2*v)) | |
| =SCANBYCOL(,CHAR(TRANSPOSE(SEQUENCE(5,5,65))),CONCAT) | |
| =SCANBYCOL("",CHAR(TRANSPOSE(SEQUENCE(5,5,65))),LAMBDA(a,v,TEXTJOIN("|",,a,v))) | |
| SAMPLE 1: "https://www.mrexcel.com/board/threads/running-totals-in-2d-dynamic-array.1267671/" | |
| =SCANBYCOL(0,TOROW(Table12[Possible Numbers])=Table11[Numbers],SUM) | |
| SAMPLE 2: "https://techcommunity.microsoft.com/discussions/excelgeneral/fill-blanks-down-up-or-both-in-columns---similar-to-power-query/3993011" | |
| =LET( | |
| init, BYCOL(Table2, LAMBDA(col, IFERROR(TAKE(TRIMRANGE(col, 1), 1), ""))), | |
| DROP(SCANBYCOL(, VSTACK(init, Table2), LAMBDA(a,v, IF(v = "", a, v))), 1) | |
| ) | |
| */ | |
| SCANBYCOL = LAMBDA(initial_value,array,function, | |
| LET( | |
| rs, CHAR(30), | |
| DROP( | |
| TRANSPOSE( | |
| SCAN( | |
| initial_value, | |
| TRANSPOSE(EXPAND(array, ROWS(array) + 1,, rs)), | |
| LAMBDA(a,v, IF(IFERROR(v = rs, 0), initial_value, IF(ISOMITTED(a), v, function(a, v)))) | |
| ) | |
| ), | |
| -1 | |
| ) | |
| ) | |
| ); | |
| // ALTERNATIVE DEFINITION (requires SCANBYROW): | |
| SCANBYCOL = LAMBDA(initial_value,array,function, | |
| TRANSPOSE( | |
| SCANBYROW(initial_value, TRANSPOSE(array), function) | |
| ) | |
| ); | |
| /* FUNCTION NAME: SCAN3 | |
| DESCRIPTION: Scans up to 3 input arrays by applying a LAMBDA to each value of each array and returns a single array of intermediate results. | |
| PREREQUISITES: VALS2, VALS3 | |
| SYNTAX: =SCAN3(initial_value,array,function_or_array2,[function_or_array3],[function]) | |
| ARGUMENTS: initial_value (required) - the starting value for the accumulator (referenced in the first iteration). | |
| array (required) - the first array to be scanned. | |
| function_or_array2, [function_or_array3] - a LAMBDA with a parameter for the accumulator plus one for each array specified, or another array to be scanned. | |
| [function] (optional) - a custom LAMBDA function with 4 parameters, to be used only when all 3 arrays have been specified. | |
| REMARKS: [1] When multiple arrays have been specified, initial_value CANNOT be omitted. Use "", 0, or another appropriate value to initialize the procedure. | |
| If initial_value is omitted, the first iteration of SCAN will ignore the function argument altogether and return the first value in the array, | |
| which will be an uncalled LAMBDA function (TYPE 128) after VALS2 or VALS3 has been applied, causing the first iteration to return #CALC!. | |
| [2] initial_value, array and function_or_array2 are required; all subsequent arguments are optional. | |
| [3] If only 1 array is specified, the LAMBDA function should be entered in the function_or_array2 argument. | |
| [4] If only 2 arrays are specified, the LAMBDA function should be entered in the [function_or_array3] argument. | |
| [5] Maintains the same high-level of efficiency with both range and array objects. | |
| EXAMPLES: =SCAN3(0,WEEKDAY(Table1[Date],2)<>1,IF(Table1[Stat?],0,Table1[Hours]),LAMBDA(acc,add,hrs,acc*add+hrs)) | |
| =SCAN3("",B2:M6,EXPAND(EXPAND(1,ROWS(B2:M6),,1),,COLUMNS(B2:M6),0),LAMBDA(a,v,b,IF(v="",IF(b,"",a),v))) | |
| =TRANSPOSE(SCAN3("",TRANSPOSE(Table2),EXPAND(EXPAND(1,COLUMNS(Table2),,1),,ROWS(Table2),0),LAMBDA(a,v,b,IF(v="",IF(b,"",a),v)))) | |
| SAMPLE 1: "https://stackoverflow.com/questions/79228362/restarting-and-resuming-a-running-total-for-multiple-values" | |
| =SCAN3(0,Table2[Price],Table2[Brand]=DROP(Table2[[#All],[Brand]],-1),LAMBDA(a,v,b,v+a*b)) | |
| SAMPLE 2: "https://www.excelforum.com/excel-formulas-and-functions/1436539-looping-like-scan-but-multiple-in-and-outputs.html" | |
| =MAP( | |
| SCAN3( | |
| VALS3(, 170, -8.75), | |
| {10;8;8;7;10;13;15;0;0;0;15}, | |
| {20;19;19;18;17;19;20;22;23;21;20}, | |
| LAMBDA(a,in,ex, | |
| LET( | |
| q, a(2) + a(3) + in, | |
| t, q / 500 * 25 + 15, | |
| v, (t - ex) * -2.5, | |
| VALS3(t, q, v) | |
| ) | |
| ) | |
| ), | |
| LAMBDA(a, a(1)) | |
| ) | |
| */ | |
| SCAN3 = LAMBDA(initial_value,array,function_or_array2,[function_or_array3],[function], | |
| LET( | |
| num, 3 - ISOMITTED(function_or_array3) - ISOMITTED(function), | |
| fnλ, CHOOSE(num, function_or_array2, function_or_array3, function), | |
| CHOOSE( | |
| num, | |
| SCAN(initial_value, array, fnλ), | |
| SCAN(initial_value, MAP(array, function_or_array2, VALS2), | |
| LAMBDA(acc,val, fnλ(acc, val(1), val(2)))), | |
| SCAN(initial_value, MAP(array, function_or_array2, function_or_array3, VALS3), | |
| LAMBDA(acc,val, fnλ(acc, val(1), val(2), val(3)))) | |
| ) | |
| ) | |
| ); | |
| /* FUNCTION NAME: SCAN5 | |
| DESCRIPTION: Scans up to 5 input arrays by applying a LAMBDA to each value of each array and returns a single array of intermediate results. | |
| PREREQUISITES: VALS2, VALS3, VALS4, VALS5 | |
| SYNTAX: =SCAN5(initial_value,array,function_or_array2,[function_or_array3],[function_or_array4],[function_or_array5],[function]) | |
| ARGUMENTS: initial_value (required) - the starting value for the accumulator (referenced in the first iteration). | |
| array (required) - the first array to be scanned. | |
| function_or_array2, [function_or_array3], ... - a LAMBDA with a parameter for the accumulator plus one for each array specified, or another array to be scanned. | |
| [function] (optional) - a custom LAMBDA function with 6 parameters, to be used only when all 5 arrays have been specified. | |
| REMARKS: [1] When multiple arrays have been specified, initial_value CANNOT be omitted. Use "", 0, or another appropriate value to initialize the procedure. | |
| If initial_value is omitted, the first iteration of SCAN will ignore the function argument altogether and return the first value in the array, | |
| which will be an uncalled LAMBDA function (TYPE 128) after VALS2, VALS3, etc. has been applied, causing the first iteration to return #CALC!. | |
| [2] initial_value, array and function_or_array2 are required; all subsequent arguments are optional. | |
| [3] If only 1 array is specified, the LAMBDA function should be entered in the function_or_array2 argument. | |
| [4] If only 2 arrays are specified, the LAMBDA function should be entered in the [function_or_array3] argument. | |
| [5] If only 3 arrays are specified, the LAMBDA function should be entered in the [function_or_array4] argument. | |
| [6] If only 4 arrays are specified, the LAMBDA function should be entered in the [function_or_array5] argument. | |
| [7] Maintains the same high-level of efficiency with both range and array objects. | |
| EXAMPLES: *Same as SCAN3 shown above, but with 2 extra arguments for additional input arrays.* | |
| */ | |
| SCAN5 = LAMBDA(initial_value,array,function_or_array2,[function_or_array3],[function_or_array4],[function_or_array5],[function], | |
| LET( | |
| num, 5 - ISOMITTED(function_or_array3) - ISOMITTED(function_or_array4) - ISOMITTED(function_or_array5) - ISOMITTED(function), | |
| fnλ, CHOOSE(num, function_or_array2, function_or_array3, function_or_array4, function_or_array5, function), | |
| CHOOSE( | |
| num, | |
| SCAN(initial_value, array, fnλ), | |
| SCAN(initial_value, MAP(array, function_or_array2, VALS2), | |
| LAMBDA(acc,val, fnλ(acc, val(1), val(2)))), | |
| SCAN(initial_value, MAP(array, function_or_array2, function_or_array3, VALS3), | |
| LAMBDA(acc,val, fnλ(acc, val(1), val(2), val(3)))), | |
| SCAN(initial_value, MAP(array, function_or_array2, function_or_array3, function_or_array4, VALS4), | |
| LAMBDA(acc,val, fnλ(acc, val(1), val(2), val(3), val(4)))), | |
| SCAN(initial_value, MAP(array, function_or_array2, function_or_array3, function_or_array4, function_or_array5, VALS5), | |
| LAMBDA(acc,val, fnλ(acc, val(1), val(2), val(3), val(4), val(5)))) | |
| ) | |
| ) | |
| ); | |
| /* FUNCTION NAME: REDUCE3 | |
| DESCRIPTION: Reduces up to 3 input arrays to an accumulated value by applying a LAMBDA to each value of each array and returns the total accumulated value. | |
| PREREQUISITES: VALS2, VALS3 | |
| SYNTAX: =REDUCE3(initial_value,array,function_or_array2,[function_or_array3],[function]) | |
| ARGUMENTS: initial_value (required) - the starting value for the accumulator (referenced in the first iteration). | |
| array (required) - the first array to be reduced. | |
| function_or_array2, [function_or_array3] - a LAMBDA with a parameter for the accumulator plus one for each array specified, or another array to be reduced. | |
| [function] (optional) - a custom LAMBDA function with 4 parameters, to be used only when all 3 arrays have been specified. | |
| REMARKS: [1] When multiple arrays have been specified, initial_value should NOT be omitted. Use "", 0, or another appropriate value to initialize the procedure. | |
| If initial_value is omitted, the first iteration of REDUCE will ignore the function argument altogether and return the first value in the array, | |
| which will be an uncalled LAMBDA function (TYPE 128) after VALS2 or VALS3 has been applied, causing the first iteration to return #CALC!. | |
| [2] initial_value, array and function_or_array2 are required; all subsequent arguments are optional. | |
| [3] If only 1 array is specified, the LAMBDA function should be entered in the function_or_array2 argument. | |
| [4] If only 2 arrays are specified, the LAMBDA function should be entered in the [function_or_array3] argument. | |
| [5] Maintains the same high-level of efficiency with both range and array objects. | |
| SAMPLE 1: "https://www.reddit.com/r/excel/comments/1m1t4ml/can_you_convert_a_recursive_formula_from_using_a/" | |
| =REDUCE3( | |
| "My leetspeak Admin Password", | |
| {"A","a","e","i","o","u","l","S","s","y"}, | |
| {4,"@",3,"!",0,2,1,5,"\$",7}, | |
| LAMBDA(acc,pat,rep, REGEXREPLACE(acc, pat, rep)) | |
| ) | |
| */ | |
| REDUCE3 = LAMBDA(initial_value,array,function_or_array2,[function_or_array3],[function], | |
| LET( | |
| num, 3 - ISOMITTED(function_or_array3) - ISOMITTED(function), | |
| fnλ, CHOOSE(num, function_or_array2, function_or_array3, function), | |
| CHOOSE( | |
| num, | |
| REDUCE(initial_value, array, fnλ), | |
| REDUCE(initial_value, MAP(array, function_or_array2, VALS2), | |
| LAMBDA(acc,val, fnλ(acc, val(1), val(2)))), | |
| REDUCE(initial_value, MAP(array, function_or_array2, function_or_array3, VALS3), | |
| LAMBDA(acc,val, fnλ(acc, val(1), val(2), val(3)))) | |
| ) | |
| ) | |
| ); | |
| /* FUNCTION NAME: REDUCE5 | |
| DESCRIPTION: Reduces up to 5 input arrays to an accumulated value by applying a LAMBDA to each value of each array and returns the total accumulated value. | |
| PREREQUISITES: VALS2, VALS3, VALS4, VALS5 | |
| SYNTAX: =REDUCE5(initial_value,array,function_or_array2,[function_or_array3],[function_or_array4],[function_or_array5],[function]) | |
| ARGUMENTS: initial_value (required) - the starting value for the accumulator (referenced in the first iteration). | |
| array (required) - the first array to be reduced. | |
| function_or_array2, [function_or_array3], ... - a LAMBDA with a parameter for the accumulator plus one for each array specified, or another array to be reduced. | |
| [function] (optional) - a custom LAMBDA function with 6 parameters, to be used only when all 5 arrays have been specified. | |
| REMARKS: [1] When multiple arrays have been specified, initial_value should NOT be omitted. Use "", 0, or another appropriate value to initialize the procedure. | |
| If initial_value is omitted, the first iteration of REDUCE will ignore the function argument altogether and return the first value in the array, | |
| which will be an uncalled LAMBDA function (TYPE 128) after VALS2, VALS3, etc. has been applied, causing the first iteration to return #CALC!. | |
| [2] initial_value, array and function_or_array2 are required; all subsequent arguments are optional. | |
| [3] If only 1 array is specified, the LAMBDA function should be entered in the function_or_array2 argument. | |
| [4] If only 2 arrays are specified, the LAMBDA function should be entered in the [function_or_array3] argument. | |
| [5] If only 3 arrays are specified, the LAMBDA function should be entered in the [function_or_array4] argument. | |
| [6] If only 4 arrays are specified, the LAMBDA function should be entered in the [function_or_array5] argument. | |
| [7] Maintains the same high-level of efficiency with both range and array objects. | |
| EXAMPLES: *Same as REDUCE3 shown above, but with 2 extra arguments for additional input arrays.* | |
| */ | |
| REDUCE5 = LAMBDA(initial_value,array,function_or_array2,[function_or_array3],[function_or_array4],[function_or_array5],[function], | |
| LET( | |
| num, 5 - ISOMITTED(function_or_array3) - ISOMITTED(function_or_array4) - ISOMITTED(function_or_array5) - ISOMITTED(function), | |
| fnλ, CHOOSE(num, function_or_array2, function_or_array3, function_or_array4, function_or_array5, function), | |
| CHOOSE( | |
| num, | |
| REDUCE(initial_value, array, fnλ), | |
| REDUCE(initial_value, MAP(array, function_or_array2, VALS2), | |
| LAMBDA(acc,val, fnλ(acc, val(1), val(2)))), | |
| REDUCE(initial_value, MAP(array, function_or_array2, function_or_array3, VALS3), | |
| LAMBDA(acc,val, fnλ(acc, val(1), val(2), val(3)))), | |
| REDUCE(initial_value, MAP(array, function_or_array2, function_or_array3, function_or_array4, VALS4), | |
| LAMBDA(acc,val, fnλ(acc, val(1), val(2), val(3), val(4)))), | |
| REDUCE(initial_value, MAP(array, function_or_array2, function_or_array3, function_or_array4, function_or_array5, VALS5), | |
| LAMBDA(acc,val, fnλ(acc, val(1), val(2), val(3), val(4), val(5)))) | |
| ) | |
| ) | |
| ); | |
| /* FUNCTION NAME: EVALS | |
| DESCRIPTION: Returns all items from a vector of multi-value elements, or a specified item from an array of multi-value elements. | |
| PREREQUISITES: EVALS_NUM | |
| SYNTAX: =EVALS(vals_vector,[num],[exclusive]) | |
| ARGUMENTS: vals_vector (required) - the vector or array of multi-value elements to be evaluated (derived using VALS2, VALS3, etc.). | |
| [num] (optional) - the total number of items or the exclusive id number of the item to be extracted from each element (auto-detect, if omitted). | |
| [exclusive] (Boolean, optional) - specifies which items are to be extracted from each element. | |
| 0 or FALSE - all items from 1 to the specified [num] (default) | |
| 1 or TRUE - the specified id [num] only | |
| SAMPLE 1: "https://www.excelforum.com/excel-formulas-and-functions/1436539-looping-like-scan-but-multiple-in-and-outputs.html" | |
| =EVALS( | |
| SCAN3( | |
| VALS3(, 170, -8.75), | |
| {10;8;8;7;10;13;15;0;0;0;15}, | |
| {20;19;19;18;17;19;20;22;23;21;20}, | |
| LAMBDA(a,in,ex, | |
| LET( | |
| q, a(2) + a(3) + in, | |
| t, q / 500 * 25 + 15, | |
| v, (t - ex) * -2.5, | |
| VALS3(t, q, v) | |
| ) | |
| ) | |
| ) | |
| ) | |
| SAMPLE 2: "https://www.mrexcel.com/board/threads/create-variable-interest-rate-amortization-schedule-using-dynamic-arrays.1269465/" | |
| =LET( | |
| yrs, 2, | |
| ppy, 12, | |
| amt, 50000, | |
| dur, ppy * yrs, | |
| tbl, {1,0.03125;7,0.0475;23,0.0375}, | |
| VALS7, LAMBDA([_1],[_2],[_3],[_4],[_5],[_6],[_7],LAMBDA(x,CHOOSE(x,_1,_2,_3,_4,_5,_6,_7))), | |
| VSTACK( | |
| {"Period","Beg Bal","Rate","Payment","Interest","Principal","End Bal"}, | |
| EVALS( | |
| SCAN( | |
| VALS7(,,,,,, amt), | |
| SEQUENCE(dur), | |
| LAMBDA(a,v, | |
| LET( | |
| beg, a(7), | |
| apr, VLOOKUP(v, tbl, 2, 1), | |
| ppr, apr / ppy, | |
| pmt, -PMT(ppr, dur - v + 1, beg), | |
| int, beg * ppr, | |
| ppl, pmt - int, | |
| end, beg - ppl, | |
| VALS7(v, beg, apr, pmt, int, ppl, end) | |
| ) | |
| ) | |
| ) | |
| ) | |
| ) | |
| ) | |
| */ | |
| EVALS = LAMBDA(vals_vector,[num],[exclusive], | |
| LET( | |
| num, IF(ISOMITTED(num), EVALS_NUM(@vals_vector), num), | |
| IF( | |
| exclusive, | |
| MAP(vals_vector, LAMBDA(val, val(num))), | |
| LET( | |
| k, IF(COLUMNS(vals_vector) = 1, SEQUENCE(, num), SEQUENCE(num)), | |
| MAP(IFNA(vals_vector, k), IFNA(k, vals_vector), LAMBDA(val, x, val(x))) | |
| ) | |
| ) | |
| ) | |
| ); | |
| /* FUNCTION NAME: EVALS_NUM | |
| DESCRIPTION: (recursive) Attempts to auto-detect the number of items contained within a multi-value element. | |
| SYNTAX: =EVALS_NUM(val,[k]) | |
| ARGUMENTS: val (required) - the multi-value element to be evaluated (derived using VALS2, VALS3, etc.). | |
| [k] (integer, internal use only) - the id number of the current iteration. | |
| REMARKS: May return incorrect results if an item contains a #VALUE! error. | |
| EXAMPLES: *Not applicable (for internal use with the EVALS function shown above).* | |
| */ | |
| EVALS_NUM = LAMBDA(val,[k], | |
| LET( | |
| k, IF(ISOMITTED(k), 3, k + 1), | |
| IF(IFNA(ERROR.TYPE(@+val(k)), 0) = 3, k - 1, EVALS_NUM(val, k)) | |
| ) | |
| ); | |
| /* FUNCTION NAME: VALS2 | |
| DESCRIPTION: Stores up to 2 variables in a single element (TYPE 128), which can be recalled by id number as needed. | |
| SYNTAX: =VALS2([value1],[value2]) | |
| ARGUMENTS: [value1], [value2] - the value, cell, range or array to be stored for later use; both arguments are optional. | |
| EXAMPLES: =LET( | |
| lbl, VALS2(Table1[Dept.], Table1[[#Headers],[Jan]:[Mar]]), | |
| HSTACK(TOCOL(lbl({1})), TOCOL(lbl({2})), TOCOL(Table1[[Jan]:[Mar]])) | |
| ) | |
| SAMPLE 1: "https://www.reddit.com/r/excel/comments/1m1t4ml/can_you_convert_a_recursive_formula_from_using_a/" | |
| =REDUCE( | |
| "My leetspeak Admin Password", | |
| MAP({"A","a","e","i","o","u","l","S","s","y"}, {4,"@",3,"!",0,2,1,5,"\$",7}, VALS2), | |
| LAMBDA(acc,val, REGEXREPLACE(acc, val(1), val(2))) | |
| ) | |
| SAMPLE 2: "https://techcommunity.microsoft.com/discussions/excelgeneral/formula-to-count-the-number-of-cells-including-the-one-which-repeats-itself/4137759" | |
| =MAP( | |
| SCAN(VALS2(), A2:A200, LAMBDA(λ,v, LET(a, LAMBDA(x, IF(ISOMITTED(x), v, x))(λ(1)), | |
| IF(AND(COUNTIF(a:v, v) = 2, v <> ""), VALS2(, COUNT(a:v)), VALS2(a, ""))))), | |
| LAMBDA(λ, λ(2)) | |
| ) | |
| */ | |
| VALS2 = LAMBDA([value1],[value2], | |
| LAMBDA(x, CHOOSE(x, value1, value2)) | |
| ); | |
| /* FUNCTION NAME: VALS3 | |
| DESCRIPTION: Stores up to 3 variables in a single element (TYPE 128), which can be recalled by id number as needed. | |
| SYNTAX: =VALS3([value1],[value2],[value3]) | |
| ARGUMENTS: [value1], [value2], ... - the value, cell, range or array to be stored for later use; all arguments are optional. | |
| EXAMPLES: *Same as VALS2 shown above, but with 1 extra argument for an additional variable.* | |
| */ | |
| VALS3 = LAMBDA([value1],[value2],[value3], | |
| LAMBDA(x, CHOOSE(x, value1, value2, value3)) | |
| ); | |
| /* FUNCTION NAME: VALS4 | |
| DESCRIPTION: Stores up to 4 variables in a single element (TYPE 128), which can be recalled by id number as needed. | |
| SYNTAX: =VALS4([value1],[value2],[value3],[value4]) | |
| ARGUMENTS: [value1], [value2], ... - the value, cell, range or array to be stored for later use; all arguments are optional. | |
| EXAMPLES: *Same as VALS2 shown above, but with 2 extra arguments for additional variables.* | |
| */ | |
| VALS4 = LAMBDA([value1],[value2],[value3],[value4], | |
| LAMBDA(x, CHOOSE(x, value1, value2, value3, value4)) | |
| ); | |
| /* FUNCTION NAME: VALS5 | |
| DESCRIPTION: Stores up to 5 variables in a single element (TYPE 128), which can be recalled by id number as needed. | |
| SYNTAX: =VALS5([value1],[value2],[value3],[value4],[value5]) | |
| ARGUMENTS: [value1], [value2], ... - the value, cell, range or array to be stored for later use; all arguments are optional. | |
| EXAMPLES: *Same as VALS2 shown above, but with 3 extra arguments for additional variables.* | |
| */ | |
| VALS5 = LAMBDA([value1],[value2],[value3],[value4],[value5], | |
| LAMBDA(x, CHOOSE(x, value1, value2, value3, value4, value5)) | |
| ); | |
| /* FUNCTION NAME: LTrimChar | |
| DESCRIPTION: (recursive) Removes leading characters from a text string or array. | |
| SYNTAX: =LTrimChar(text,char,[case_sensitive]) | |
| ARGUMENTS: text (required) - the value or array to be cleaned. | |
| char (required) - the character(s) to be trimmed. | |
| [case_sensitive] (Boolean, optional) - specifies case-sensitivity. | |
| 0 or FALSE - not case-sensitive (default) | |
| 1 or TRUE - case-sensitive | |
| EXAMPLES: =LTrimChar(A2:A10,"0") | |
| =LTrimChar(A2:A10,0,TRUE) | |
| =LTrimChar(A2:A10,"CON") | |
| =LTrimChar(A2:A10,"CON",1) | |
| */ | |
| LTrimChar = LAMBDA(text,char,[case_sensitive], | |
| LET( | |
| arr, LEFT(text, LEN(char)), | |
| opt, NOT(case_sensitive), | |
| tst, IF(opt, arr = char, EXACT(arr, char)), | |
| IF(OR(tst), LTrimChar(IF(tst, TEXTAFTER(text, char,, opt), text), char, case_sensitive), text) | |
| ) | |
| ); | |
| /* FUNCTION NAME: RTrimChar | |
| DESCRIPTION: (recursive) Removes trailing characters from a text string or array. | |
| SYNTAX: =RTrimChar(text,char,[case_sensitive]) | |
| ARGUMENTS: text (required) - the value or array to be cleaned. | |
| char (required) - the character(s) to be trimmed. | |
| [case_sensitive] (Boolean, optional) - specifies case-sensitivity. | |
| 0 or FALSE - not case-sensitive (default) | |
| 1 or TRUE - case-sensitive | |
| EXAMPLES: =RTrimChar(A2:A10,"0") | |
| =RTrimChar(A2:A10,0,TRUE) | |
| =RTrimChar(A2:A10,"x") | |
| =RTrimChar(A2:A10,"x",1) | |
| */ | |
| RTrimChar = LAMBDA(text,char,[case_sensitive], | |
| LET( | |
| arr, RIGHT(text, LEN(char)), | |
| opt, NOT(case_sensitive), | |
| tst, IF(opt, arr = char, EXACT(arr, char)), | |
| IF(OR(tst), RTrimChar(IF(tst, TEXTBEFORE(text, char, -1, opt), text), char, case_sensitive), text) | |
| ) | |
| ); | |
| /* FUNCTION NAME: TRIMCHAR | |
| DESCRIPTION: Removes leading and/or trailing characters from a text string or array. | |
| PREREQUISITES: LTrimChar, RTrimChar | |
| SYNTAX: =TRIMCHAR(text,[char],[trim_mode],[case_sensitive]) | |
| ARGUMENTS: text (required) - the value or array to be cleaned. | |
| [char] (optional) - the character(s) to be trimmed (default is "0"). | |
| [trim_mode] (integer, optional) - determines which characters should be trimmed. | |
| 0 - None | |
| 1 - Leading | |
| 2 - Trailing | |
| 3 - Both (default) | |
| [case_sensitive] (Boolean, optional) - specifies case-sensitivity. | |
| 0 or FALSE - not case-sensitive (default) | |
| 1 or TRUE - case-sensitive | |
| EXAMPLES: =TRIMCHAR(A2:A10) | |
| =TRIMCHAR(A2:A10,,1) | |
| =TRIMCHAR(A2:A10,"x",2) | |
| =TRIMCHAR(A2:A10,{"CB","0"},1) | |
| =TRIMCHAR(A2:A10,{0,"x"},2,TRUE) | |
| =MAP(A2:A10,TRIMCHAR) | |
| */ | |
| TRIMCHAR = LAMBDA(text,[char],[trim_mode],[case_sensitive], | |
| REDUCE( | |
| text, | |
| IF(ISOMITTED(char), "0", char), | |
| LAMBDA(txt,chr, | |
| CHOOSE( | |
| IF(ISOMITTED(trim_mode), 3, trim_mode) + 1, | |
| txt, | |
| LTrimChar(txt, chr, case_sensitive), | |
| RTrimChar(txt, chr, case_sensitive), | |
| RTrimChar(LTrimChar(txt, chr, case_sensitive), chr, case_sensitive) | |
| ) | |
| ) | |
| ) | |
| ); | |
| /* FUNCTION NAME: BETWEEN | |
| DESCRIPTION: A helper function for writing dynamic criteria to filter a table or array by values that fall within a specified range. | |
| SYNTAX: =BETWEEN(field,[value1],[value2],[incl_mode]) | |
| ARGUMENTS: field (required) - the column to filter by. | |
| [value1] (optional) - sets the lower bound of the range (ignored if omitted). | |
| [value2] (optional) - sets the upper bound of the range (ignored if omitted). | |
| [incl_mode] (integer, optional) - specifies whether the range is inclusive of value1 and/or value2. | |
| 0 - greater than value1 and less than value2 | |
| 1 - greater than or equal to value1 and less than value2 | |
| 2 - greater than value1 and less than or equal to value2 | |
| 3 - greater than or equal to value1 and less than or equal to value2 (default) | |
| EXAMPLES: =FILTER(Table1,BETWEEN(Table1[Year],2022)) | |
| =FILTER(Table1,BETWEEN(Table1[Year],,2018)) | |
| =FILTER(Table1,BETWEEN(Table1[Year],2020,2024)) | |
| =FILTER(Table1,BETWEEN(Table1[Price],10000.01,24999.99)) | |
| =FILTER(Table1,BETWEEN(Table1[Price],10000,25000,0)) | |
| =FILTER(Table1,BETWEEN(Table1[Price],10000,24999.99)) | |
| =FILTER(Table1,BETWEEN(Table1[Price],10000,25000,1)) | |
| =FILTER(Table1,BETWEEN(Table1[Price],10000.01,25000)) | |
| =FILTER(Table1,BETWEEN(Table1[Price],10000,25000,2)) | |
| */ | |
| BETWEEN = LAMBDA(field,[value1],[value2],[incl_mode], | |
| LET( | |
| i, IF(ISOMITTED(incl_mode), 3, incl_mode), | |
| CHOOSE( | |
| 1 + ISBLANK(value1) + ISBLANK(value2) * 2, | |
| CHOOSE( | |
| 1 + i, | |
| (field > value1) * (field < value2), | |
| (field >= value1) * (field < value2), | |
| (field > value1) * (field <= value2), | |
| (field >= value1) * (field <= value2) | |
| ), | |
| IF(i > 1, field <= value2, field < value2), | |
| IF(MOD(i, 2), field >= value1, field > value1), | |
| EXPAND(1, ROWS(field),, 1) | |
| ) | |
| ) | |
| ); | |
| /* FUNCTION NAME: INCLUDE | |
| DESCRIPTION: A helper function for writing dynamic criteria to filter a table or array by text that equals, begins with, ends with, or contains. | |
| SYNTAX: =INCLUDE(field,criteria,[incl_mode],[case_sensitive]) | |
| ARGUMENTS: field (required) - the column to filter by. | |
| criteria (required) - the condition(s) to filter by; accepts multiple conditions to "filter by list". | |
| [incl_mode] (integer, optional) - specifies the type of filter to be applied. | |
| 0 - Equals... (default) | |
| 1 - Begins with... | |
| 2 - Ends with... | |
| 3 - Contains... | |
| [case_sensitive] (Boolean, optional) - specifies case-sensitivity. | |
| 0 or FALSE - not case-sensitive (default) | |
| 1 or TRUE - case-sensitive | |
| EXAMPLES: =FILTER(Table1,INCLUDE(Table1[Type],"used")) | |
| =FILTER(Table1,INCLUDE(Table1[Year],{2025,2026})) | |
| =FILTER(Table1,INCLUDE(Table1[Stock No.],"U",1,TRUE)) | |
| =FILTER(Table1,INCLUDE(Table1[Make],{"GM","chev"},1)) | |
| =FILTER(Table1,INCLUDE(Table1[VIN],{"208233"},2)) | |
| =FILTER(Table1,INCLUDE(Table1[VIN],{208233,208236},2,1)) | |
| =FILTER(Table1,INCLUDE(Table1[Exterior Color],"blue",3)) | |
| =FILTER(Table1,INCLUDE(Table1[Model],{"Sierra","Silver"},3,1)) | |
| */ | |
| INCLUDE = LAMBDA(field,criteria,[incl_mode],[case_sensitive], | |
| IF( | |
| AND(ISBLANK(criteria)), | |
| EXPAND(1, ROWS(field),, 1), | |
| LET( | |
| arr, SORT(TOROW(criteria, 1),,, 1), | |
| opt, 1 + (COLUMNS(arr) = 1) + AND(case_sensitive) * 2, | |
| CHOOSE( | |
| incl_mode + 1, | |
| CHOOSE( | |
| opt, | |
| ISNUMBER(XMATCH(field, arr,, 2)), | |
| field = arr, | |
| BYROW(EXACT(field, arr), OR), | |
| EXACT(field, arr) | |
| ), | |
| CHOOSE( | |
| opt, | |
| BYROW(LEFT(field, LEN(arr)) = arr, OR), | |
| LEFT(field, LEN(arr)) = arr, | |
| BYROW(EXACT(LEFT(field, LEN(arr)), arr), OR), | |
| EXACT(LEFT(field, LEN(arr)), arr) | |
| ), | |
| CHOOSE( | |
| opt, | |
| BYROW(RIGHT(field, LEN(arr)) = arr, OR), | |
| RIGHT(field, LEN(arr)) = arr, | |
| BYROW(EXACT(RIGHT(field, LEN(arr)), arr), OR), | |
| EXACT(RIGHT(field, LEN(arr)), arr) | |
| ), | |
| CHOOSE( | |
| opt, | |
| BYROW(ISNUMBER(SEARCH(arr, field)), OR), | |
| ISNUMBER(SEARCH(arr, field)), | |
| BYROW(ISNUMBER(FIND(arr, field)), OR), | |
| ISNUMBER(FIND(arr, field)) | |
| ) | |
| ) | |
| ) | |
| ) | |
| ); | |
| /* FUNCTION NAME: LIKE | |
| DESCRIPTION: A helper function for writing dynamic criteria to filter a table or range using wildcards and comparison operators. | |
| SYNTAX: =LIKE(range,criteria,[and_or]) | |
| ARGUMENTS: range (required) - the table column(s) to filter by; accepts multiple columns; must be a range reference. | |
| criteria (required) - the condition(s) to filter by; accepts multiple conditions, wildcards and comparison operators. | |
| [and_or] (TYPE:=128, optional) - an eta-reduced LAMBDA that specifies the logic used when multiple conditions are present. | |
| OR - returns TRUE if at least one condition is met (default) | |
| XOR - returns TRUE if only one condition is met | |
| AND - returns TRUE if all conditions are met | |
| EXAMPLES: =FILTER(Table1,LIKE(Table1[Year],">2022")) | |
| =FILTER(Table1,LIKE(Table1[Year],"<=2018")) | |
| =FILTER(Table1,LIKE(Table1[Year],{">=2020","<=2024"},AND)) | |
| =FILTER(Table1,LIKE(Table1[Type],"used")) | |
| =FILTER(Table1,LIKE(Table1[Year],{"2025","2026"})) | |
| =FILTER(Table1,LIKE(Table1[Stock No.],"U*")) | |
| =FILTER(Table1,LIKE(Table1[Make],{"GM*","chev*"})) | |
| =FILTER(Table1,LIKE(Table1[Stock No.],"*A")) | |
| =FILTER(Table1,LIKE(Table1[VIN],"*20823?")) | |
| =FILTER(Table1,LIKE(Table1[Exterior Color],"*blue*")) | |
| =FILTER(Table1,LIKE(Table1[Model],{"*sierra*","*silver*"})) | |
| =FILTER(Table1,LIKE(Table1[Model],"silver*hd")) | |
| =FILTER(Table1,LIKE(Table1[[Model]:[Exterior Color]],{"sierra*","*blue*"},AND)) | |
| */ | |
| LIKE = LAMBDA(range,criteria,[and_or], | |
| LET( | |
| fnλ, IF( | |
| TYPE(criteria) = 64, | |
| LET( | |
| and_or, IF(ISOMITTED(and_or), OR, and_or), | |
| LAMBDA(rng, and_or(COUNTIF(rng, criteria))) | |
| ), | |
| LAMBDA(rng, COUNTIF(rng, criteria)) | |
| ), | |
| BYROW(range, fnλ) | |
| ) | |
| ); | |
| /* FUNCTION NAME: Nz | |
| DESCRIPTION: Returns a zero-length string or another specified value when a variant is null (derived from a blank cell in a range). | |
| SYNTAX: =Nz(range,[value_if_blank]) | |
| ARGUMENTS: range (required) - the range to be evaluated. | |
| [value_if_blank] (optional) - the value to be returned if a cell within the range is blank (default is ""). | |
| EXAMPLES: =LTrimChar(Nz(A2:A100),"x") | |
| =Nz(TRIMCHAR(A2:A100,"x",2,1)) | |
| =FILTER(Table1,LIKE(Table1[Make],TEXTSPLIT(Nz(B4,"<>λ")," "))) | |
| =FILTER(Table1,LIKE(Table1[Year],{">=";"<="}&Nz(B1:B2,{0;9999}),AND)) | |
| */ | |
| Nz = LAMBDA(range,[value_if_blank], | |
| IF( | |
| ISBLANK(range), | |
| IF(ISOMITTED(value_if_blank), "", value_if_blank), | |
| range | |
| ) | |
| ); | |
Author
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
@harun24hr I'm glad you liked them. Thanks again for all of your feedback and support!