Candidate Functions for the LAMBDA Library

This page lists some functions which might end up as officially documented functions in the LAMBDA Library. For now, they need to be tested, evaluated, researched, improved, etc.

The code is meant to be copy/pasted directly into the AFE in the Excel Labs add-in. The code on this page is subject to change without notice or version tracking.

Graduates

1/2/2026 - MAP_IJ, COLMAT, ROWMAT
12/10/2025 - ORA & ANDA, SORTBYLIST, REMOVEONCE, FilterOut became FILTERBYLIST
1/28/2026 - TEXT2ARRAY
6/3/2026 - Address Functions - AddressToRow, AddressToCol, GetAddressesByValue, ToLocalAddress, ToGlobalAddress, IndexByAddress, LocalAddress (previously RelativeAddress), OffsetByAddress
6/3/2026 - Map Functions - Distance, DistanceFrom, GetAdjAddresses, ArrowEffect, PathByDirection, PathViewable

Array and Matrix Functions

shuffleArray(array,[dimension])

/**
* Shuffle Rows (dim=1, default) or Columns (dim=2)
*/
shuffleArray = LAMBDA(array,[dimension],
LET(doc,"https://www.vertex42.com/lambda/candidates.html",
    version,"Beta",
    dim,IF(ISOMITTED(dimension),1,dimension),
    array,IF(dim=1,array,TRANSPOSE(array)),
    rand,RANDARRAY(ROWS(array),1),
    shuffled,DROP(SORT(HSTACK(rand,array)),0,1),
    IF(dim=1,shuffled,TRANSPOSE(shuffled))
));

CountUnique(array,[ignore_blanks],[sort_order])

Something like this function is a must-have for Esport challenges. It uses GROUPBY to turn an array into a column, find the unique values, and then count the occurrences of each of the unique values.

/**
* Finds the UNIQUE values in array, and returns the count of each
* sort_order defaults to -2 (the second column in descending order)
*/
CountUnique = LAMBDA(array,[ignore_blanks],[sort_order],
LET(doc,"https://www.vertex42.com/lambda/candidates.html",
    version,"Beta",
    ignore_blanks,IF(ISOMITTED(ignore_blanks),FALSE,ignore_blanks),
    sort_order,IF(ISOMITTED(sort_order),-2,sort_order),
    GROUPBY(TOCOL(array,ignore_blanks),TOCOL(array,ignore_blanks),ROWS,0,0,sort_order)
));

ReplaceMatchingRows(orig_array, new_array, cols_to_match)

/**
* Replace Matching Rows based on XMATCH(,,0) for the specified columns
* Use Case: Updating the Quantities of an Inventory Table [Grocery Store Cleanup Case by Harry Seiders]
*/
ReplaceMatchingRows = LAMBDA(orig_array, new_array, cols_to_match,
LET(doc,"https://www.vertex42.com/lambda/candidates.html",
    version,"Beta",
    // Ensure that cols_to_match is a row vector without blanks or errors
    cols_to_match,TOROW(cols_to_match,3),
    // Create a matrix to be used as the filter_condition
    match_array,DROP(REDUCE("",cols_to_match,LAMBDA(acc,col,
        HSTACK(acc,IF(ISERROR( XMATCH(CHOOSECOLS(orig_array,col),CHOOSECOLS(new_array,col),0) ), 0, 1))
    )),,1),
    // For rows to match, the rowsum must be equal to the number of cols_to_match
    filter_condition, ROWSUM(match_array)=COLUMNS(cols_to_match),
    // Remove matching rows from orig_array, then append new_array
    final_array,VSTACK(FILTER(orig_array,NOT(filter_condition)),new_array),
    final_array
));

AllElementsMatch

I frequently want to test that all corresponding elements of two arrays match.

/**
* Return TRUE if each Aij=Bij (All corresponding elements of arrays A and B are equal)
*/
AllElementsMatch = LAMBDA(array_1,array_2,
LET(doc,"https://www.vertex42.com/lambda/candidates.html",
    version,"Beta",
    cells,ROWS(array_1)*COLUMNS(array_1),
    match,array_1=array_2,
    SUM(1*match)=cells
));

NthOccurrence

/**
* Get the position of the Nth occurrence of one of the target values within an array.
*/
NthOccurrence = LAMBDA(find_values, within_array, nth,
LET(doc,"https://www.vertex42.com/lambda/candidates.html",
    version, "Beta",
    // Step 1: Match the elements in the array to the target values
    is_target_code, ISNUMBER(XMATCH(within_array, find_values)),
    // Step 2: Generate a cumulative count for the matching values
    cumulative_count, SCAN(0, is_target_code, LAMBDA(acc, match, acc + 1 * match)),
    // Step 3: Find the position of the Nth occurrence
    nth_position, XMATCH(nth, cumulative_count),
    // Step 4: Return the corresponding position or #N/A if doesn't exist
    res, IF(ISNA(nth_position), NA(), nth_position),
    res
));

TRIMARRAY

This one is for trimming when TRIMRANGE is not enough.

/**
* Trim an array to its smallest bounding rectangle by removing outer
* rows/columns that contain nothing but the trim condition.
*/
/*
* Inputs:
*   array       : Range/array to trim.
*   [trim_value]: (Optional) Either:
*                 - A function returning TRUE for "trim" cells (default ISBLANK), or
*                 - A scalar value to match (e.g., 0, "X", ""), treated as trim.
* Usage:
*   =TRIMARRAY(array)
*   =TRIMARRAY(array, 0)
*   =TRIMARRAY(array, "")
*   =TRIMARRAY(array, ISNA)
*   =TRIMARRAY(array, LAMBDA(v,EXACT(v,"X")) )
* Notes:
*   - Only removes OUTER full-trim rows/cols (does not delete interior rows/cols).
*   - If everything is trimmed, returns NA().
*/
TRIMARRAY = LAMBDA(array,[trim_value],
LET(doc,"https://www.vertex42.com/lambda/candidates.html",
    version,"1/2/2026 - Beta",
    nR,ROWS(array),
    nC,COLUMNS(array),
    // Build predicate: isTrim(value) -> TRUE/FALSE
    isTrim,
        IF(ISOMITTED(trim_value),
            ISBLANK, // Default
            IF(TYPE(trim_value)=64,
                // trim_value is an array
                LAMBDA(v,
                    ISNUMBER(XMATCH(v,TOCOL(trim_value)))
                ),
            IF(OR(TYPE(trim_value)=8,TYPE(trim_value)=128),
                // trim_value is a function (e.g. ISBLANK, ISNA, LAMBDA)
                trim_value,
                // trim_value is single value
            LAMBDA(v, v=trim_value )
            ))
        ),
    // Row/Col keep flags computed directly
    keepRow, BYROW(array, LAMBDA(r, OR( NOT(isTrim(r)) ))),
    keepCol, BYCOL(array, LAMBDA(c, OR( NOT(isTrim(c)) ))),
    // Find first/last TRUE in keepRow/keepCol
    r_top,  IFERROR(XMATCH(TRUE, keepRow, 0, 1), NA()),
    r_bot,  IFERROR(XMATCH(TRUE, keepRow, 0,-1), NA()),
    c_left, IFERROR(XMATCH(TRUE, keepCol, 0, 1), NA()),
    c_right,IFERROR(XMATCH(TRUE, keepCol, 0,-1), NA()),
    // Counts to drop
    drop_t,    r_top - 1,
    drop_b, nR - r_bot,
    drop_l,   c_left - 1,
    drop_r,  nC - c_right,
    res,IF(OR(ISNA(r_top), ISNA(c_left)),
        NA(),
        DROP(DROP(array,drop_t,drop_l),-drop_b,-drop_r)
    ),
    res
));

Wrapping Functions similar to IFNA, IFERROR

IFNA(values,value_if_na) and IFERROR(values,value_if_error) are built-in Excel functions that avoid having to include a complex expression twice. These are great because you can basically just "wrap" an existing expression to convert errors to other values.

Before LET or IFERROR, the method was: =IF(ISERROR(expr),expr,value_if_false). With IFERROR this was simplified to =IFERROR(expr,value_if_false). With LET, you could use =LET(expr,expression,IF(ISERROR(expr),value_if_error,expr). Using LET avoids having to repeat the expression twice, but it's still a little bit complicated.

The IFIS function below converts an existing "IS..." function such as ISEVEN or ISBLANK or ISFORMULA into an "IF..." function like IFNA or IFERROR.

/**
* Converts IS... functions into IF... functions
* Example: =IFIS(values,ISBLANK,"")
**/
IFIS = LAMBDA(values,function,value_if_true,
LET(doc,"https://www.vertex42.com/lambda/candidates.html",
    version,"Beta",
    MAP(values,LAMBDA(val,IF(function(val),value_if_true,val)))
));

IFBLANK is such a commonly needed function, like IFNA it deserves to be a built-in Excel function.

/**
* Replaces values that are blank using MAP
* OARobot has an IFBLANK function that does exactly this
*/
IFBLANK = LAMBDA(values,value_if_blank,
LET(doc,"https://www.vertex42.com/lambda/candidates.html",
    version,"Beta",
    MAP(values,LAMBDA(val,IF(ISBLANK(val),value_if_blank,val)))
));

LAMBDA Function Helpers / Utilities

VSTACK and HSTACK ignoring parameters that evaluate to #N/A, #CALC!, etc.

VSTACKE was designed to handle cases like =VSTACK( TOCOL(A1:A5,1), TOCOL(B1:B5,1), ... ) where the arrays might be empty or evaluate to an error like #N/A or #CALC!. At least one of the parameters still needs to be non-empty or non-error. For HSTACKE, just change VSTACK to HSTACK throughout the code.

✅ Vote for this suggestion

/**
* Version of VSTACK that ignores NA() and #CALC! errors as parameters
*/
VSTACKE = LAMBDA([array_1],[array_2],[array_3],[array_4],[array_5],
LET(doc,"https://www.vertex42.com/lambda/candidates.html",
    step_1,IF(ISERROR(ROWS(array_1)),NA(),array_1),
    isErr_2,ISERROR(ROWS(array_2)),
    step_2,IF(ISERROR(ROWS(step_1)),
      IF(isErr_2,NA(),array_2),
      IF(isErr_2,step_1,VSTACK(step_1,array_2))
    ),
    isErr_3,ISERROR(ROWS(array_3)),
    step_3,IF(ISERROR(ROWS(step_2)),
      IF(isErr_3,NA(),array_3),
      IF(isErr_3,step_2,VSTACK(step_2,array_3))
    ),
    isErr_4,ISERROR(ROWS(array_4)),
    step_4,IF(ISERROR(ROWS(step_3)),
      IF(isErr_4,NA(),array_4),
      IF(isErr_4,step_3,VSTACK(step_3,array_4))
    ),
    isErr_5,ISERROR(ROWS(array_5)),
    step_5,IF(ISERROR(ROWS(step_4)),
      IF(isErr_5,NA(),array_5),
      IF(isErr_5,step_4,VSTACK(step_4,array_5))
    ),
    step_5
));

/**
* PRNG
* Deterministic pseudo-random U(0,1) from an integer step n (works with arrays).
*/
/*
* Inputs:
*   n      : integer step index (scalar or array)
*   [seed] : optional stream id (numeric); default = 1, (use 1 ... 1e6)
* Output:
*   U in [0,1) with same shape as n
* Notes:
* - Non-volatile: only changes when n/seed change.
* - Reproducible across recalcs and sessions.
* - For a second independent draw at the same n, pass a different seed (e.g., seed+10007).
* Disclaimer:
*   This PRNG is a lightweight, hash-style generator intended for demos, visuals,
*   toy simulations, and teaching. It is NOT cryptographically secure and has not
*   been subjected to rigorous statistical test batteries. It may exhibit correlations
*   or artifacts on certain sampling patterns. Do not use for security, gambling, 
*   compliance-critical modeling, or research requiring formally validated randomness.
*/
PRNG = LAMBDA(n,[seed],
LET(doc,"https://www.vertex42.com/lambda/candidates.html",
    version,"beta",
    seed,IF(ISOMITTED(seed),1,seed),
    MOD( SIN((n+seed*1e6)*12.9898+78.233)*43758.5453, 1)
));

Text Manipulation

/**
* Split Text and Numbers into an Array, "A3B-4C6.3" would become
* {"A",3,"B",-4,"C",6.3}. Numbers converted to values.
* Use Case: Split "↑47→6" into {"↑",47,"→",6}
*/
SplitTextAndNumbers = LAMBDA(text, [max_iter], [usetrim],
LET(doc, "https://www.vertex42.com/lambda/candidates.com",
    usetrim, IF(ISOMITTED(usetrim),TRUE,usetrim),
    numpattern, "[+-]?\d+(?:\.\d+)?",
    extracted, REGEXEXTRACT(text,"(?:"&numpattern&"|(?:(?!"&numpattern&").)+)",1),
    IFERROR(VALUE(extracted),IF(usetrim,TRIM(extracted),extracted))
));

/**
* CIPHERSHIFT(text, shift_n)
* Caesar-shift letters by shift_n (wraps mod 26), case-sensitive. Non-letters pass through.
*/
/*
* Inputs:
*   text     : A single string, a single character, or an array of strings.
*   shift_n  : Integer shift; can be negative. Normalized via MOD(shift_n,26).
* Output:
*   Same shape as text. Each string is Caesar-shifted A–Z / a–z, other chars unchanged.
* Output / Notes:
*   - Splits characters via REGEXEXTRACT(cell,".",1) to handle Unicode better than LEN/MID.
*   - Uses UNICODE/UNICHAR. Only ASCII letters [A–Z] / [a–z] are shifted; others are returned as-is.
*   - Shift wraps with MOD(…,26). Case is preserved.
*
* Examples:
*   CIPHERSHIFT("G",5)            -> "L"
*   CIPHERSHIFT("Wklv Rqh",-3)    -> "This One"
*   CIPHERSHIFT("This One",3)     -> "Wklv Rqh"
*   MAP({"Hi","Zz"},LAMBDA(s,CIPHERSHIFT(s,1))) -> {"Ij","Aa"}
*   =CONCAT(CIPHERSHIFT(TEXT2ARRAY("This One"),3)) -> "Wklv Rqh"
*/
CIPHERSHIFT = LAMBDA(text,shift_n,
LET(doc, "https://www.vertex42.com/lambda/candidates.com",
    shift,MOD(shift_n,26),
    MAP(text,
        LAMBDA(cell,
            CONCAT(MAP(REGEXEXTRACT(cell,".",1),LAMBDA(a,
            LET(
                u, UNICODE(a),
                isUpper, AND(u>=65, u<=90),
                isLower, AND(u>=97, u<=122),
                isLetter, OR(isUpper, isLower),
                base, IF(isUpper, 65, 97),
                u_new, IF(isLetter, base + MOD(u - base + shift, 26), u),
                UNICHAR(u_new)
            ))))
        )
    )
));

PRNG(n, seed), Pseudo-Random Number Generator

Sometimes you want a random number for a very basic simulation, but you want your simulation to be repeatable and nonvolatile. That is the purpose of this function, and note the diclaimer in the comments below.

Another common method for repeatable random simulations is to generate a static array of random numbers and use INDEX(rand_array,n).

Outline Numbering

It has been a goal of mine for a while now to create a lambda that can handle a lot of different outline numbering styles, and I'm making progress. Here are some of the functions in the works.

/**
 * IncrementNumberInText - Extracts the first integer from a string, increments it, 
 * and reinserts it into the original text in the same position.
 */
/*
 * Inputs:
 *   text: The original text containing the number.
 *   increment: The integer to increment the extracted number by.
 *
 * Outputs:
 *   Returns the original text with the integer incremented.
 *
 * Notes:
 *   - The function uses REGEXEXTRACT and "-?[0-9]+"
 *   - Supports negative integers and leading zeros (009 becomes 010)
 *   - Modifies only the first integer in the text
 *   - Does not work with decimals, formatted number (e.g. 1,000), or multiple numbers.
 *
 * Use cases: (2), 1., a2, 3b, Turn 5, Player 1, Left -1, DX0010, Version 03
 */
IncrementNumInText = LAMBDA(text, [increment],
LET(doc, "https://www.vertex42.com/lambda/candidates.html",
    version, "Beta",
    // If text is blank, should return "" instead of 0
    text, IF(ISBLANK(text),"",text),
    // Check if text is actually a number
    is_number, ISNUMBER(text),
    // Default increment is 1
    increment, IF( ISBLANK(increment), 1,
        // Validate increment (must be an integer)
        IF(INT(increment)=increment, increment, NA() )
    ),
    // Extract the first number from the text
    number, IFNA( REGEXEXTRACT(text, "-?[0-9]+"), ""),
    // Increment the number, handling leading zeros if needed
    incremented_number, IF(number = "", "", 
        TEXT(VALUE(number) + increment, REPT("0", LEN(SUBSTITUTE(number,"-",""))))
    ),
    // Replace the original number in the text with the incremented number
    result, IF( number = "", text, SUBSTITUTE(text, number, incremented_number, 1) ),
    // If original value was a number, return NA()
    IF( is_number,
        NA(),
        result
    )
));

/**
 * IncrementCharInText - Increments the first standalone letter or address-style string
 * such as (a) to (b), 1a. to 1b., C) to D), or AAZ42 to ABA42.
 */
/*
 * Inputs:
 *   text: The original text containing the character or address to increment.
 *   [increment]: The amount to increment (default = 1). Must be an integer.
 *
 * Outputs:
 *   Returns the text with the first character or column-address incremented.
 *
 * Notes:
 *   - Handles Excel-style wrapping (e.g., Z to AA, BD to BE) using OffsetByAddress.
 *   - Preserves case (lowercase or uppercase) of the original string.
 *   - Returns the original text if no valid letter string is found.
 *   - Returns #N/A if the increment is invalid or the input is numeric.
 *
 * Use cases: (a), b., C), 1a, HA45, B2, a., 2a)
 */
IncrementCharInText = LAMBDA(text, [increment],
LET(doc, "https://www.vertex42.com/lambda/candidates.html",
    version, "Beta",
    // If text is blank, should return "" instead of 0
    text, IF(ISBLANK(text),"",text),
    // Check if text is actually a number
    is_number, ISNUMBER(text),
    // Default increment is 1
    increment, IF( ISBLANK(increment), 1,
        // Validate increment (must be an integer)
        IF(INT(increment)=increment, increment, NA() )
    ),
    // Extract the first 1-3 char string not preceded or followed by letters
    str, IFNA( REGEXEXTRACT(text, "(?<![A-Za-z])[A-Za-z]{1,3}(?![A-Za-z])"), ""),
    // Increment using OffsetByAddress to handle Z to AA, BD to BE, etc.
    incremented_adr, IF(str = "", "", 
        SUBSTITUTE(OffsetByAddress( str&1, 0, increment ),"1","")
    ),
    // If original string was all lowercase, return lowercase, otherwise will be upper
    incremented, IF(EXACT(str,LOWER(str)),LOWER(incremented_adr),incremented_adr),
    // Replace the original string in the text with the incremented value
    result, IF( str = "", text, SUBSTITUTE(text, str, incremented, 1) ),
    // If original value was a number, return NA()
    IF( is_number,
        NA(),
        result
    )
));