SHIFT

=SHIFT(array, [row_offset], [col_offset], [pad_with])

Description

The SHIFT function allows you to shift all elements of an array based on a row_offset and col_offset. Think of it like using OFFSET(value,row_offset,col_offset,1,1) for each individual value in the array, except that when the offset results in a value outside the bounds of the array, the pad_with value is returned instead.

=LET(
    array, {
        "🪨","🧱","🪨";
        "🧱","🧱","🪨";
        "🪨","🪨","🧱"
    },
    SHIFT(array,-1,-1)
)

Result:
    {"",  "",  "";
     "","🪨","🧱";
     "","🧱","🧱"}

Unlike CIRCSHIFT, which rotates rows or columns n times in a circular manner, SHIFT was built to use OFFSET-like inputs, and truncates edges rather than cycling.

SHIFT was designed as a building block for efficient element-wise sliding window algorithms, and inspired by the various "map" cases common in Excel Esports (and in particular by Hadyn Wiseman demonstrating the use of an ArrayShift function). It uses EXPAND(DROP()) when the offsets are positive and stacks the padded value when offsets are negative.

Lambda Formula

This code for using SHIFT in Excel is provided under the License as part of the LAMBDA Library, but to use just this function, you may copy the following code directly into your spreadsheet.

/**
* Returns a same-size array with values shifted by row_offset and col_offset.
* Padded with "" or value of choice.
*/
/*
* Inputs:
*   array        : The array or range to shift
*   [row_offset] : (Default=0) A single integer value. (e.g. Up = -1)
*   [col_offset] : (Default=0) A single integer value. (e.g. Left = -1)
*   [pad_with]   : (Default="") The value to use for replacing out-of-bounds
* Notes:
*   - EXPAND(DROP()) is more efficient than padding with VSTACK/HSTACK
*   - Inputs are coerced to integers using TRUNC
*/
SHIFT = LAMBDA(array,[row_offset],[col_offset],[pad_with],
LET(doc,"https://www.vertex42.com/lambda/shift.html",
    version,"1/2/2026 - Beta",
    row_offset,IF(ISOMITTED(row_offset),0,TRUNC(row_offset)),
    col_offset,IF(ISOMITTED(col_offset),0,TRUNC(col_offset)),
    pad_with,IF(ISOMITTED(pad_with),"",pad_with),
    nR,ROWS(array),
    nC,COLUMNS(array),
    rOff, MAX(-nR, MIN(nR, row_offset)),
    cOff, MAX(-nC, MIN(nC, col_offset)),
    dropped,LAMBDA( DROP(array,rOff,cOff) ),
    fnPad, LAMBDA(r,c, MAKEARRAY(r,c, LAMBDA(i,j, pad_with))),
    padRow,LAMBDA( arr, VSTACK(fnPad(ABS(rOff),COLUMNS(arr)),arr) ),
    padCol,LAMBDA( arr, HSTACK(fnPad(ROWS(arr),ABS(cOff)),arr) ),
    result,
        IF( AND(rOff=0,cOff=0),
            array,
        IF( OR(ABS(rOff)=nR,ABS(cOff)=nC),
            fnPad(nR,nC),
        IF( rOff<0,
            IF( cOff<0,
                padCol(padRow(dropped())),
                EXPAND(padRow(dropped()),nR,nC,pad_with)
            ),
            IF( cOff<0,
                EXPAND(padCol(dropped()),nR,nC,pad_with),
                EXPAND(dropped(),nR,nC,pad_with)
            )
        ))),
    result
));

SHIFT Examples

=LET(
    array, {
        "🪨","🧱","🪨";
        "🧱","🧱","🪨";
        "🪨","🪨","🧱"
    },
    arr_above,SHIFT(array,-1,0),
    arr_below,SHIFT(array,1,0),
    arr_left,SHIFT(array,0,-1),
    arr_right,SHIFT(array,0,1),
    rock, "🪨",
    (arr_above=rock)+(arr_below=rock)+(arr_left=rock)+(arr_right=rock)
)

Result:
    {0,2,1;
     2,2,1;
     1,1,2}

CountNeighbors

=CountNeighbors(array,[valid_value],[masktype],[include_center],[pad_with])

Here is a more generalized lambda for the above Example 2 which counts neighboring values that match a valid_value. You can choose from the following mask types: 1=ortho, 2=diag, 3=all, 4=UpDown, 5=LeftRight.

/**
* CountNeighbors - For each cell in array, returns the count of neighboring
* values equal to valid_value based on a masktype (1=ortho, 2=diag,
* 3=All8Neighbors, 4=UpDown, 5=LeftRight). Requires SHIFT.
*/
CountNeighbors = LAMBDA([array],[valid_value],[masktype],[include_center],[pad_with],
IF(ISOMITTED(array),"Usage: masktype(1=ortho,2=diag,3=all8,4=UpDown,5=LeftRight,8=all8)",
LET(doc,"https://www.vertex42.com/lambda/shift.html",
    version,"1/2/2026 - Beta",
    valid_value,IF(ISOMITTED(valid_value),TRUE,valid_value),
    masktype,IF(ISOMITTED(masktype),1,masktype),
    include_center,IF(ISOMITTED(include_center),FALSE,include_center),
    pad_with,IF(ISOMITTED(pad_with),"'~'",pad_with),
    thOrtho,LAMBDA(
        (SHIFT(array,1)=valid_value) +
        (SHIFT(array,-1)=valid_value) +
        (SHIFT(array,,1)=valid_value) +
        (SHIFT(array,,-1)=valid_value)
        ),
    thDiag,LAMBDA(
        (SHIFT(array,1,1)=valid_value) +
        (SHIFT(array,1,-1)=valid_value) +
        (SHIFT(array,-1,1)=valid_value) +
        (SHIFT(array,-1,-1)=valid_value)
        ),
    thCenter,LAMBDA( IF(include_center,array=valid_value,0) ),
    thCenter() + IF(OR(masktype=1,masktype="ortho"),thOrtho(),
    IF(OR(masktype=2,masktype="diag"),thDiag(),
    IF(OR(masktype=3,masktype="all",masktype=8),thOrtho()+thDiag(),
    IF(OR(masktype=4,masktype="UD",masktype="TB"),
        (SHIFT(array,1)=valid_value) + (SHIFT(array,-1)=valid_value),
    IF(OR(masktype=5,masktype="LR"),
        (SHIFT(array,,1)=valid_value) + (SHIFT(array,,-1)=valid_value),
        "Unhandled Masktype (1=ortho,2=diag,3=All8Neighbors,4=UpDown,5=LeftRight)"
    )))))
)
));

See Also

CIRCSHIFT

Acknowledgements

The SHIFT lambda function was inspired by Hadyn Wiseman demonstrating a solution to a monster maze case by Harry Gross.