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.

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))
));

dealArray(array,[number],[players],[pad_width])

This is something that can be accomplished easily using TAKE and WRAPROWS, but the idea is to take a sequence of "cards" from an array and hand them out one at a time to each player. The result is an array where the columns are the starting player's hands.

/**
* Deal N Cards from an Array (single row or column) of Cards to P Players
*/
dealArray = LAMBDA(array_vector,[number],[players],[pad_with],
LET(doc,"https://www.vertex42.com/lambda/candidates.html",
    version,"Beta",
    number,IF(ISOMITTED(number),2,number),
    players,IF(ISOMITTED(players),2,players),
    pad_width,IF(ISOMITTED(pad_with),NA(),pad_with),
    array,IF(ROWS(array_vector)>1,array_vector,TRANSPOSE(array_vector)),
    TAKE(WRAPROWS(array,players,pad_with),number)
));

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
));

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)))
));

Filter Help

filterOut(remove_list,from_list)

/**
* Remove values in remove_list from from_list
* remove_list is a single-column or single-row array
* match_col (default 1) specifies which column of from_list to use for matching
*/
filterOut = LAMBDA(remove_list,from_array,[match_col],
LET(doc,"https://www.vertex42.com/lambda/candidates.html",
    version,"Beta",
    match_col,IF(ISOMITTED(match_col),1,match_col),
    FILTER(from_array,ISERROR(XMATCH(INDEX(from_array,,match_col),remove_list,0)),FALSE)
));

Element-Wise OR and AND

✅ Vote for this suggestion

/**
* Element-Wise OR for Arrays. All array values should be boolean (or 0,1) and the same size.
*/
ORA = LAMBDA(array_1,array_2,[array_3],[array_4],[array_5],[array_6],
LET(doc,"https://www.vertex42.com/lambda/candidates.html",
    version,"Beta",
    (array_1+array_2+array_3+array_4+array_5+array_6)>0
));
/**
* Element-Wise AND for Arrays. All array values should be boolean (or 0,1) and the same size.
*/
ANDA = LAMBDA(array_1,array_2,[array_3],[array_4],[array_5],[array_6],
LET(doc,"https://www.vertex42.com/lambda/candidates.html",
    version,"Beta",
    array_3,IF(ISOMITTED(array_3),1,array_3),
    array_4,IF(ISOMITTED(array_4),1,array_4),
    array_5,IF(ISOMITTED(array_5),1,array_5),
    array_6,IF(ISOMITTED(array_6),1,array_6),
    res,(array_1*array_2*array_3*array_4*array_5*array_6)>0,
    res
));

RemoveOnce : Remove one of Each Item from an Array

The idea here is to remove one of each of the items in remove_list from from_array, based on match_col which defaults to the first column of from_array. This is a sequential algorithm using REDUCE.

Use Case: Game simulation - removing a known set of cards from a complete deck.

/**
* Remove one of each item from an array
* remove_items is a single-column or single-row array
* match_col (default 1) specifies which column of from_list to use for matching
*/
RemoveOnce = LAMBDA(remove_items, from_array, [match_col],
LET(doc,"https://www.vertex42.com/lambda/candidates.html",
    version,"Beta",
    match_col,IF(ISOMITTED(match_col),1,match_col),
    final_array, REDUCE(from_array, remove_items,
        LAMBDA(current_array, item,
            LET(
                match_position, XMATCH(item, INDEX(current_array,,match_col) ),
                IF(ISNUMBER(match_position),
                    LET(
                        index_range, SEQUENCE(ROWS(current_array)),
                        FILTER(current_array, index_range<>match_position)
                    ),
                    current_array
                )
            )
        )
    ),
    final_array
));

ADDRESS Helpers

Although INDIRECT is very effective to get the addresses for an array using A1 notation, the following set of functions allow you to do this without using INDIRECT. My tests show that these are slightly slower than INDIRECT, but if you are more concerned about the use of a volatile function than 1000s of iterations of the function, these are options:

/**
* Gets the Row number from a text address like "BZ45" without INDIRECT
* Alternate: ROW(INDIRECT(address_text))
*/
AddressToRow = LAMBDA(address_text,
LET(doc,"https://www.vertex42.com/lambda/candidates.html",
    version, "Beta",
    VALUE(REGEXEXTRACT(address_text, "[0-9]+"))
));
/**
* Gets the Column number from a text address like "BZ45" without INDIRECT
* Alternate: COLUMN(INDIRECT(address_text))
*/
AddressToCol = LAMBDA(address_text,
LET(doc,"https://www.vertex42.com/lambda/candidates.html",
    version, "Beta",
    col_labels,UPPER(REGEXEXTRACT(address_text,"[A-Za-z]+")),
    rows,ROWS(address_text),
    cols,COLUMNS(address_text),
    lengths, LEN(col_labels),
    // Create an array for each character position in the labels
    position_arrays, SEQUENCE(MAX(lengths),,1),
    max_length, MAX(lengths),
    // Convert character to values via CODE, then add
    pos_1,CODE(MID(col_labels,lengths,1))-64,
    pos_2,IF(max_length>1,
        IFERROR(CODE(MID(col_labels,lengths-1,1))-64,0)*26,
        0
    ),
    pos_3,IF(max_length>2,
        IFERROR(CODE(MID(col_labels,lengths-2,1))-64,0)*676,
        0
    ),
    pos_1+pos_2+pos_3
));
/**
* Allows you to reference the relative location in an array using "A1" notation
*/
IndexByAddress = LAMBDA(array, address,
LET(doc,"https://www.vertex42.com/lambda/candidates.html",
    version, "Beta",
    INDEX(array, AddressToRow(address), AddressToCol(address))
));
/**
* Creates an array of addresses, relative to the upper-left cell being A1
*/
RelativeAddress = LAMBDA(array, [abs_num],
LET(doc,"https://www.vertex42.com/lambda/candidates.html",
    version, "Beta",
    abs_num, IF(ISOMITTED(abs_num), 1, abs_num),
    ADDRESS(
        SEQUENCE(ROWS(array)),
        SEQUENCE(1, COLUMNS(array)),
        abs_num
    )
));
/**
 * OffsetByAddress - (Non-Volatile) Generate a range of cell addresses offset from a starting address.
 */
OffsetByAddress = LAMBDA(start_address, rows, cols, [width], [height],
LET(doc, "https://www.vertex42.com/lambda/candidates.html",
    version, "Beta",
    width, IF(ISBLANK(width), 1, width),
    height, IF(ISBLANK(height), 1, height),
    start_is_array, OR( ROWS(start_address) > 1, COLUMNS(start_address) > 1 ),
    start_row, AddressToRow(start_address),
    start_col, AddressToCol(start_address),
    row_vec, IF( start_is_array, start_row,
        SEQUENCE(height, 1, start_row + rows, 1)
    ),
    col_vec, IF( start_is_array, start_col,
        SEQUENCE(1, width, start_col + cols, 1)
    ),
    row_array, IF( start_is_array, start_row + rows, 
        MMULT(row_vec, SEQUENCE(1, width, 1, 0))
    ),
    col_array, IF( start_is_array, start_col + cols, 
        MMULT(SEQUENCE(height, 1, 1, 0), col_vec)
    ),
    res, ADDRESS(row_array, col_array, 4),
    res
));

/**
* Get the cell addresses of a specific value from within an array.
*   By default, addresses are relative with the upper-left cell being A1.
*/
GetAddressesByValue = LAMBDA(value, map_array, [abs_num], [relative_addresses],
LET(doc,"https://www.vertex42.com/lambda/candidates.html",
    version, "Beta",
    abs_num, IF(ISOMITTED(abs_num), 4, abs_num),
    relative_addresses, IF(ISOMITTED(relative_addresses), TRUE, relative_addresses),
    addresses, IF(relative_addresses,
        TOCOL(RelativeAddress(map_array, abs_num)),
        TOCOL(ADDRESS(ROW(map_array), COLUMN(map_array), abs_num))
    ),
    FILTER(addresses, TOCOL(map_array) = value, "Not Found")
));

Map Helpers

To go along with the Address Functions above, I've been working on some lambdas to help solve some of the common "map" challenges. These are primarily ideas for now.


/**
* Calculates the Distance (Manhattan or Chebyshev) from Address1 to Address2
* using text-based addresses. Example: =Distance({"A1";"A5";"E20"},"D8",TRUE)
*/
Distance = LAMBDA(from,to,[allow_diagonals],[count_current],
LET(doc,"https://www.vertex42.com/lambda/candidates.html",
    version,"Beta",
    allow_diagonals,IF(ISOMITTED(allow_diagonals),FALSE,allow_diagonals),
    count_current,IF(ISOMITTED(count_current),FALSE,count_current),
    rows, ABS(AddressToRow(from)-AddressToRow(to)),
    cols, ABS(AddressToCol(from)-AddressToCol(to)),
    res,IF(allow_diagonals,
        MAP(rows,cols,LAMBDA(a,b,MAX(a,b))),
        MAP(rows,cols,LAMBDA(a,b,a+b))
    ),
    IF(count_current,1+res,res)
));

/**
* Return the valid adjacent addresses from a specific address in an array.
* valid_value is "All" by default. allow_diagonals is TRUE by default.
*/
/*
* Inputs:
*   location: The starting cell address (e.g., "B2").
*   map_array: The 2D array or range representing the map or grid.
*   [valid_value]: (Optional) The value to consider as valid. If omitted or empty, all cells are valid.
*                             Use "All" to allow any value. Using "" will allow only blank cells.
*   [allow_diagonals]: (Optional) Boolean to specify if diagonal movement is allowed. Defaults to TRUE.
*
* Outputs:
*   - Returns an array of valid adjacent locations based on the given criteria.
*   - Returns NA() if no valid locations are found
*
* Notes:
*   - Supports both cardinal (N, S, E, W) and diagonal directions (if enabled).
*   - Checks if the adjacent cells are within bounds and optionally match the valid_value.
*/
GetAdjAddresses = LAMBDA(location, map_array, [valid_value], [allow_diagonals],
LET(doc,"https://www.vertex42.com/lambda/candidates.html",
    version, "Beta",
    // Map dimensions
    map_rows, ROWS(map_array),
    map_cols, COLUMNS(map_array),
    // Default parameters
    valid_value, IF(ISOMITTED(valid_value), "", valid_value),
    allow_diagonals, IF(ISOMITTED(allow_diagonals), TRUE, allow_diagonals),
    // Convert the current location to row/column indices
    current_row, ROW(INDIRECT(location)),
    current_col, COLUMN(INDIRECT(location)),
    // Define possible directions (N, S, E, W + diagonals if enabled)
    row_offsets, IF(allow_diagonals, {-1, 1, 0, 0, -1, -1, 1, 1}, {-1, 1, 0, 0}),
    col_offsets, IF(allow_diagonals, {0, 0, 1, -1, -1, 1, -1, 1}, {0, 0, 1, -1}),
    // Check valid locations
    valid_locations, MAP(
        row_offsets, col_offsets,
        LAMBDA(row_offset, col_offset,
            LET(
                new_row, current_row + row_offset,
                new_col, current_col + col_offset,
                // Check if within grid bounds and matches valid_value (if specified)
                valid, AND(
                    // Are the new locations within grid bounds?
                    new_row >= 1, new_row <= map_rows,
                    new_col >= 1, new_col <= map_cols,
                    // This is the part where grid values are checked
                    OR(valid_value = "All",
                        IFERROR(INDEX(map_array, new_row, new_col) = valid_value,FALSE)
                    )
                ),
                // Return the map location if valid, empty otherwise
                IF(valid, ADDRESS(new_row, new_col, 4), "")
            )
        )
    ),
    // Return only the valid locations
    res,IFERROR(FILTER(TOCOL(valid_locations), TOCOL(valid_locations) <> ""), NA()),
    res
));

/*
* Inputs:
*   [arrow]: (Optional) The Unicode arrow character or shorthand (e.g., "←", "L").
*            May be omitted to return the entire arrow effect table.
*   [row_or_col]: (Optional) Specify "row", 1 or "col", 2 to retrieve the row or column impact.
*                 Omit to retrieve both row and column impacts as a combined array.
*
* Outputs:
*   - Returns the row and/or column impact(s) for the specified arrow(s).
*   - If no arrow is specified, returns the entire arrow effect table.
*
* VERY STRANGE RESULT: When using res or res_2, the results cannot be used to create a sequence
*    using SEQUENCE(....,step). Rather than deal with this Excel bug, the function only allows
*    you to return either the entire array, or a lookup of the row or column effects.
*/
/**
* Retrieve the row or column impact of an arrow unicode character, based on a pre-defined table.
*/
ArrowEffect = LAMBDA([arrow],[row_or_col],
LET(doc,"https://www.vertex42.com/lambda/candidates.html",
    version,"Beta",
    arrow_effects, {"Arrow","Row_Effect","Col_Effect";
    "L",0,-1;"R",0,1;"U",-1,0;"D",1,0;"UL",-1,-1;"UR",-1,1;"LR",1,1;"LL",1,-1;
    "W",0,-1;"E",0,1;"N",-1,0;"S",1,0;"NW",-1,-1;"NE",-1,1;"SE",1,1;"SW",1,-1;
    "←",0,-1;"↑",-1,0;"→",0,1;"↓",1,0;"↖",-1,-1;"↗",-1,1;"↘",1,1;"↙",1,-1;},
    indices,IF(ISOMITTED(arrow),arrow_effects,XMATCH(arrow,INDEX(arrow_effects,,1),0)),
    res,IF(ISOMITTED(arrow),arrow_effects,
        IF(ISOMITTED(row_or_col),
            "Must Specify 1 for Row or 2 for Col",
            IF(OR(row_or_col="row",row_or_col=1),
                XLOOKUP(arrow,INDEX(arrow_effects,,1),INDEX(arrow_effects,,2)),
                XLOOKUP(arrow,INDEX(arrow_effects,,1),INDEX(arrow_effects,,3))
            )
        )
    ),
    res
));

/**
* Retrieve a string by moving in a specified direction starting from a given index in a 2D array.
*/
/*
* Inputs:
*   array: The table of data (excluding row and column labels).
*   start_row: The starting row index (1-based) in the data array.
*   start_col: The starting column index (1-based) in the data array.
*   [direction]: (Optional) The Unicode arrow character(s) or shorthand indicating the direction(s) to move.
*                Defaults to a single direction repeated based on num.
*   [num]: (Optional) The number of steps to move in the specified direction. Defaults to 1.
*   [include_start]: (Optional) Default = TRUE. Includes the starting cell value in the resulting string.
*
* Outputs: Returns the string formed by concatenating characters encountered along the path.
*
* Use Case: Built for solving tasks involving grid navigation (e.g., word searches, pathfinding).
*           See Harry Seider's INDEX_SEQUENCE case (word search).
*
* Requires: ArrowEffect
*/
PathString = LAMBDA(array, start_row, start_col, [direction], [num], [include_start],
LET(doc,"https://www.vertex42.com/lambda/candidates.html",
    version,"Beta",
    // num is optional and defaults to 1
    num, IF(ISBLANK(num), 1, num),
    // include_start is optional and defaults to TRUE
    include_start, IF(ISBLANK(include_start), TRUE, include_start),

    // Either use the array of directions, or repeat direction based on num
    direction, LET(
        val, TOCOL(direction),
        IF(ROWS(val) > 1, val, INDEX(val, SEQUENCE(MAX(1, num - 1), 1, 1, 0)))
    ),
    
    // Get the row and column impacts from the ArrowEffect function
    row_impact, ArrowEffect(direction, 1),
    col_impact, ArrowEffect(direction, 2),

    // Create the sequence of changes using a cumulative sum
    rows, SCAN(start_row, row_impact, LAMBDA(a, b, a + b)),
    cols, SCAN(start_col, col_impact, LAMBDA(a, b, a + b)),

    // Get the path values and concatenate them
    path_values, MAP(
        IF(include_start, VSTACK(start_row, rows), rows),
        IF(include_start, VSTACK(start_col, cols), cols),
        LAMBDA(r, c, INDEX(array, r, c))
    ),
    result, TEXTJOIN("", , path_values),
    result
));

LAMBDA Function Helpers / Utilities

MAP_IJ(array, LAMBDA(cell,i,j, expression) )

/**
* Like MAP for a single array, but with access to array indices
* Usage: =MAP_IJ(array, LAMBDA(cell,i,j, expression) )
*/
MAP_IJ = LAMBDA(array,function,
LET(doc,"https://www.vertex42.com/lambda/candidates.html",
    version,"Beta",
    iMat,SEQUENCE(1,SEQUENCE(1,COLUMNS(array)),SEQUENCE(ROWS(array))),
    jMat,SEQUENCE(SEQUENCE(ROWS(array)),1,SEQUENCE(1,COLUMNS(array))),
    res,MAP(array,iMat,jMat,LAMBDA(cell,i,j,function(cell,i,j))),
    res
));

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.