REPELEM Function in Excel

=REPELEM(array, [m_vert], [n_horiz])

Argument	Description	Example
array	An array or range of numbers or text	2
[m_vert]	(Optional, Default=1) Number of times to repeat the elements vertically	2
[n_horiz]	(Optional, Default=1) Number of times to repeat the elements horizontally	2

Download the Template

In the template file, navigate to the Arrays worksheet to see the REPELEM function in action.

Description

The REPARRAY function repeats (or stacks) the entire array, while REPELEM repeats each individual element of the array. This can be very useful in constructing certain types of matrices, assembling combinations, and as one of the steps to unpivot a table.

REPELEM function - Repeat Elements of an Array m x n Times

If n_horiz is blank, then m_vert will be used for n_horiz. For example, REPELEM(array,3) is the same as REPELEM(array,3,3).

One use for the combination of REPARRAY and REPELEM is for constructing an array defining all possible combinations of rows within two arrays (see COMBINATIONS).

Blank Values in Array: Normally, a dynamic array function returns 0 if values are truly blank, meaning that if a cell is empty, ISBLANK(cell) is TRUE. A cell containing ="" is not blank, because ISBLANK("") is FALSE. The [value_if_blank] optional parameter allows you to specify the value to use if a cell in the array is truly blank.

Lambda Formula

This code for using REPELEM 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.

Code to Create Function via the Name Manager

Name: REPELEM
Comment: Repeat each element of an array m times vertically and n times horizontally.
Refers To:

=LAMBDA(array,[m_vert],[n_horiz],
LET(doc,"https://www.vertex42.com/lambda/repelem.html",
    m_vert,IF(ISBLANK(m_vert),1,m_vert),
    n_horiz,IF(ISBLANK(n_horiz),1,n_horiz),
    MAKEARRAY(m_vert*ROWS(array),n_horiz*COLUMNS(array),LAMBDA(i,j,
        INDEX(array,ROUNDUP(i/m_vert,0),ROUNDUP(j/n_horiz,0))
    ))
))

Named Function for Google Sheets

Name: REPELEM
Description: Repeat each element of an array m times vertically and n times horizontally.
Arguments: array, m_vert, n_horiz (see above for descriptions and example values)
Function:

LET(doc,"https://www.vertex42.com/lambda/repelem.html",
    m_vert,IF(ISBLANK(m_vert),1,m_vert),
    n_horiz,IF(ISBLANK(n_horiz),1,n_horiz),
    MAKEARRAY(m_vert*ROWS(array),n_horiz*COLUMNS(array),LAMBDA(i,j,
        INDEX(array,ROUNDUP(i/m_vert,0),ROUNDUP(j/n_horiz,0))
    ))
)

Warning

These functions are not compatible between Excel and Google Sheets. When using these functions, you will not be able to convert the Excel file to Google Sheets or vice versa without problems.

REPELEM Examples

Example 1

Starting with the vector {1;2;3;4}, repeat each element 3 times vertically to get {1;1;1;2;2;2;3;3;3;4;4;4}

Test: Copy and Paste this LET function into a cell
=LET(
    array, {1;2;3;4},
    m_vert, 3,
    n_horiz, 1,
    REPELEM(array,m_vert,n_horiz)
)

Result: {1;1;1;2;2;2;3;3;3;4;4;4}

Example 2

Repeat each element of the square array {1,2;3,4} 2 times.

Test: Copy and Paste this LET function into a cell
=LET(
    array, {1,2;3,4},
    m_vert, 2,
    n_horiz, 2,
    REPELEM(array,m_vert,n_horiz)
)

Result: {1,1,2,2;1,1,2,2;3,3,4,4;3,3,4,4},

REPARRAY, COMBINATIONS

Revision History

12/13/2024: Changed defaults to be 1 row or column.

References & Resources

Disclaimer: This article is meant for educational purposes only. See the License regarding the LAMBDA code, and the site Terms of Use for the documentation.