Skip to content

Instantly share code, notes, and snippets.

@alekrutkowski

alekrutkowski/lambdas.md

Last active April 14, 2026 08:21
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save alekrutkowski/7847543aae6676269b300b8d40847fbe to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save alekrutkowski/7847543aae6676269b300b8d40847fbe to your computer and use it in GitHub Desktop.
Download ZIP
Useful Excel lambda functions
Raw
lambdas.md

See also https://github.com/alekrutkowski/ExcelLambdaTools

Convert 1-column range monthly dates in the format yyyy-mm, e.g. 2023-11, to 2-column range:

  • 1st column: year number if it's January, otherwise empty
  • 2nd column: the 3-character month code, e.g. "Nov"
=LAMBDA(rng,
  LET(
    d, DATE(LEFT(rng,4), MID(rng,6,2), 1),
    yr, IF(MONTH(d)=1, YEAR(d), ""),
    mon, TEXT(d, "mmm"),
    HSTACK(yr, mon)
  )
)

Example:

2021-01 2021 Jan
2021-02 Feb
2021-03 Mar
2021-04 Apr
2021-05 May
2021-06 Jun
2021-07 Jul
2021-08 Aug
2021-09 Sept
2021-10 Oct
2021-11 Nov
2021-12 ➜ Dec
2022-01 2022 Jan
2022-02 Feb
2022-03 Mar
2022-04 Apr
2022-05 May
2022-06 Jun
2022-07 Jul
2022-08 Aug
2022-09 Sept
2022-10 Oct
2022-11 Nov
2022-12 Dec

Turn a URL of a Eurostat (or any other REDISSTAT) dataset (TSV file), e.g. https://ec.europa.eu/eurostat/api/dissemination/sdmx/3.0/data/dataflow/ESTAT/prc_hicp_minr/1.0/*.*.*.*?c[freq]=M&c[unit]=I25,I15,RCH_M,RCH_A,RCH_MV12MAVR&c[coicop18]=TOTAL,CP01,CP011,CP0111,CP01111&c[geo]=EU,EU27_2020,EA,EA21,EA20,EA19&c[TIME_PERIOD]=2026-03,2026-02,2026-01,2025-12,2025-11,2025-10,2025-09,2025-08&compress=false&format=tsv into a range of data, optionally reversing the order of value columns, i.e. reversing the time order, if the optional parameter reverse_time is TRUE.

The URL can be obtaind by going to the dataset page e.g. https://ec.europa.eu/eurostat/databrowser/view/prc_hicp_minr/default/table, making your selections, clicking the [⤓] Download button on the right side, selecting Tab separated values (.tsv) in the File format dropdown menu, and clicking Copy API link button. Remember to untick Compress file (.gzip)!

=LAMBDA(url, [reverse_time],
  LET(
    rev, IF(ISOMITTED(reverse_time), TRUE, reverse_time),
    txt, WEBSERVICE(url),
    clean, SUBSTITUTE(txt, CHAR(13), ""),
    rows, FILTER(TEXTSPLIT(clean,,CHAR(10),TRUE), TEXTSPLIT(clean,,CHAR(10),TRUE)<>""),
    parsed,
      DROP(
        REDUCE(
          "",
          rows,
          LAMBDA(acc, r,
            VSTACK(
              acc,
              LET(
                cols, TEXTSPLIT(r, CHAR(9)),
                HSTACK(
                  TEXTSPLIT(INDEX(cols, 1), ","),
                  DROP(cols,,1)
                )
              )
            )
          )
        ),
        1
      ),
    hdr, TAKE(parsed,1),
    k, XMATCH(TRUE, ISNUMBER(SEARCH("\TIME_PERIOD", hdr))),
    reordered,
      IF(
        NOT(rev),
        parsed,
        IF(
          k=COLUMNS(parsed),
          parsed,
          HSTACK(
            TAKE(parsed,,k),
            CHOOSECOLS(
              DROP(parsed,,k),
              SEQUENCE(, COLUMNS(DROP(parsed,,k)), COLUMNS(DROP(parsed,,k)), -1)
            )
          )
        )
      ),
    header, SUBSTITUTE(TAKE(reordered,1), "\TIME_PERIOD", ""),
    VSTACK(header, DROP(reordered,1))
  )
)

Clean raw imported Eurostat data

Function that takes a range x and if x is:

  • a number stored as string, it will return that number,
  • :, it will return #N/A,
  • a string with a number followed by a space and some other substring (one or more flags), it will return the number,
  • any other string, it will return that string.
=LAMBDA(x,
  LET(
    _txt, TRIM(x),
    _full_num, IFERROR(VALUE(_txt), NA()),
    _gap_pos, FIND(" ", _txt & " "),
    _head_txt, LEFT(_txt, _gap_pos - 1),
    _head_num, IFERROR(VALUE(_head_txt), NA()),
    IF(
      _txt=":",
      NA(),
      IF(
        ISNUMBER(_full_num),
        _full_num,
        IF(
          ISNUMBER(_head_num),
          _head_num,
          _txt
        )
      )
    )
  )
)

Rename columns of a tabular range with column names in its top row
Usage example:
If you name this function renameColumns and A1# is the dynamic range's address:
renameColumns(A1#,{"GEO","geo";"EXPTYPE","mytype"})
column GEO becomes geo and EXPTYPE becomes mytype

=LAMBDA(tbl,pairs,
  LET(
    hdr, TAKE(tbl, 1),
    body, DROP(tbl, 1),
    old, CHOOSECOLS(pairs, 1),
    new, CHOOSECOLS(pairs, 2),
    IF(
      ROWS(UNIQUE(old))<>ROWS(old),
      "Duplicate old names in pairs",
      VSTACK(
        MAP(
          hdr,
          LAMBDA(h,
            LET(k, XMATCH(h, old, 0), IF(ISNUMBER(k), INDEX(new, k), h))
          )
        ),
        body
      )
    )
  )
)

Merge two tabular ranges by column names (data.table-style join in Excel)

Performs a join between two Excel tables or dynamic array ranges using one or more key columns specified by name. Conceptually similar to data.table::merge(), supporting multi-column keys and different join types.

Parameters

  • left_table
    A range or array representing the left table, including a header row.

  • right_table
    A range or array representing the right table, including a header row.

  • left_key_names
    Character vector (vertical or horizontal array) of column names in left_table to join on.

  • right_key_names (optional)
    Character vector of column names in right_table to join on. Defaults to left_key_names.

  • join_type (optional)
    Type of join to perform. One of:

    • "inner" – returns only rows where keys match in both tables
    • "left" – returns all rows from left_table, with matching rows from right_table where available; non-matching rows get blanks for right-side columns
    • "full" – returns all rows from both tables; non-matching rows on either side are filled with blanks

    Defaults to "inner".

Details

The function matches rows based on equality of the specified key columns. Duplicate matches are expanded (cartesian matching), so a single row in one table may produce multiple rows in the result.

The output contains all columns from left_table, followed by the non-key columns from right_table. Key columns from the right table are not duplicated.

Column matching is case-sensitive and based on exact header text. Missing matches are handled according to join_type.

Returns

A dynamic array representing the merged table, including a header row.

Examples

Inner join on same column names

=DTMERGE(A1:D10, G1:J12, {"id";"date"})

Left join with different key names

=DTMERGE(A1:D10, G1:J12,
         {"id";"date"},
         {"customer_id";"txn_date"},
         "left")

Full outer join

=DTMERGE(A1:D10, G1:J12, {"id";"date"},, "full")

Definition

Minified (for Name Manager)

=LAMBDA(left_table,right_table,left_key_names,[right_key_names],[join_type],LET(j_t_v,IF(ISOMITTED(join_type),"inner",LOWER(join_type)),l_h,TAKE(left_table,1),l_b,DROP(left_table,1),r_h,TAKE(right_table,1),r_b,DROP(right_table,1),l_k_n_v,TOCOL(left_key_names,1),r_k_n_v,IF(ISOMITTED(right_key_names),l_k_n_v,TOCOL(right_key_names,1)),l_k_p,XMATCH(l_k_n_v,l_h),r_k_p,XMATCH(r_k_n_v,r_h),r_n_k_c_p,FILTER(SEQUENCE(1,COLUMNS(r_b)),ISNA(XMATCH(SEQUENCE(1,COLUMNS(r_b)),r_k_p))),r_h_f,CHOOSECOLS(r_h,r_n_k_c_p),r_b_f,CHOOSECOLS(r_b,r_n_k_c_p),o_h,HSTACK(l_h,r_h_f),e_r_f,LAMBDA(r_v,TEXTJOIN(CHAR(30),FALSE,MAP(r_v,LAMBDA(s_v,LEN(s_v&"")&CHAR(31)&(s_v&""))))),l_k_v,BYROW(CHOOSECOLS(l_b,l_k_p),e_r_f),r_k_v,BYROW(CHOOSECOLS(r_b,r_k_p),e_r_f),r_r_f,LAMBDA(s_r,r_c,MAKEARRAY(r_c,COLUMNS(s_r),LAMBDA(r_i,c_i,INDEX(s_r,1,c_i)))),b_r_r,MAKEARRAY(1,COLUMNS(r_b_f),LAMBDA(r_i,c_i,"")),l_j_r,DROP(REDUCE("",SEQUENCE(ROWS(l_b)),LAMBDA(a_r,c_i,LET(c_l_r,INDEX(l_b,c_i,0),c_k,INDEX(l_k_v,c_i),m_r_r,FILTER(r_b_f,r_k_v=c_k,""),m_c,IF(AND(ROWS(m_r_r)=1,COLUMNS(m_r_r)=1,m_r_r=""),0,ROWS(m_r_r)),c_b,IF(m_c>0,HSTACK(r_r_f(c_l_r,m_c),m_r_r),IF(OR(j_t_v="left",j_t_v="full"),HSTACK(c_l_r,b_r_r),"")),IF(AND(ROWS(c_b)=1,COLUMNS(c_b)=1,c_b=""),a_r,VSTACK(a_r,c_b))))),1),f_j_e_r,IF(j_t_v="full",LET(u_m,ISNA(XMATCH(r_k_v,l_k_v)),u_r_r,FILTER(r_b_f,u_m,""),u_k_v,FILTER(CHOOSECOLS(r_b,r_k_p),u_m,""),u_c,IF(AND(ROWS(u_r_r)=1,COLUMNS(u_r_r)=1,u_r_r=""),0,ROWS(u_r_r)),r_l_r,IF(u_c>0,MAKEARRAY(u_c,COLUMNS(l_b),LAMBDA(r_i,c_i,LET(k_p_i_l,XMATCH(c_i,l_k_p),IF(ISNUMBER(k_p_i_l),INDEX(u_k_v,r_i,k_p_i_l),"")))),""),IF(u_c>0,HSTACK(r_l_r,u_r_r),"")),""),IF(AND(ROWS(f_j_e_r)=1,COLUMNS(f_j_e_r)=1,f_j_e_r=""),VSTACK(o_h,l_j_r),VSTACK(o_h,l_j_r,f_j_e_r))))

Full

=LAMBDA(
  left_table,
  right_table,
  left_key_names,
  [right_key_names],
  [join_type],

  LET(
    join_type_value,
      IF(ISOMITTED(join_type), "inner", LOWER(join_type)),

    left_header, TAKE(left_table, 1),
    left_body, DROP(left_table, 1),
    right_header, TAKE(right_table, 1),
    right_body, DROP(right_table, 1),

    left_key_names_vector, TOCOL(left_key_names, 1),
    right_key_names_vector,
      IF(ISOMITTED(right_key_names), left_key_names_vector, TOCOL(right_key_names, 1)),

    left_key_positions, XMATCH(left_key_names_vector, left_header),
    right_key_positions, XMATCH(right_key_names_vector, right_header),

    right_nonkey_column_positions,
      FILTER(
        SEQUENCE(1, COLUMNS(right_body)),
        ISNA(XMATCH(SEQUENCE(1, COLUMNS(right_body)), right_key_positions))
      ),

    right_header_filtered, CHOOSECOLS(right_header, right_nonkey_column_positions),
    right_body_filtered, CHOOSECOLS(right_body, right_nonkey_column_positions),

    output_header, HSTACK(left_header, right_header_filtered),

    encode_row_function,
      LAMBDA(row_values,
        TEXTJOIN(
          CHAR(30),
          FALSE,
          MAP(
            row_values,
            LAMBDA(single_value, LEN(single_value & "") & CHAR(31) & (single_value & ""))
          )
        )
      ),

    left_key_vector,
      BYROW(CHOOSECOLS(left_body, left_key_positions), encode_row_function),

    right_key_vector,
      BYROW(CHOOSECOLS(right_body, right_key_positions), encode_row_function),

    repeat_row_function,
      LAMBDA(single_row, repeat_count,
        MAKEARRAY(
          repeat_count,
          COLUMNS(single_row),
          LAMBDA(row_index, col_index, INDEX(single_row, 1, col_index))
        )
      ),

    blank_right_row,
      MAKEARRAY(1, COLUMNS(right_body_filtered), LAMBDA(row_index, col_index, "")),

    left_join_result,
      DROP(
        REDUCE(
          "",
          SEQUENCE(ROWS(left_body)),
          LAMBDA(accumulated_result, current_index,
            LET(
              current_left_row, INDEX(left_body, current_index, 0),
              current_key, INDEX(left_key_vector, current_index),

              matching_right_rows,
                FILTER(right_body_filtered, right_key_vector = current_key, ""),

              match_count,
                IF(
                  AND(
                    ROWS(matching_right_rows) = 1,
                    COLUMNS(matching_right_rows) = 1,
                    matching_right_rows = ""
                  ),
                  0,
                  ROWS(matching_right_rows)
                ),

              combined_block,
                IF(
                  match_count > 0,
                  HSTACK(
                    repeat_row_function(current_left_row, match_count),
                    matching_right_rows
                  ),
                  IF(
                    OR(join_type_value = "left", join_type_value = "full"),
                    HSTACK(current_left_row, blank_right_row),
                    ""
                  )
                ),

              IF(
                AND(
                  ROWS(combined_block) = 1,
                  COLUMNS(combined_block) = 1,
                  combined_block = ""
                ),
                accumulated_result,
                VSTACK(accumulated_result, combined_block)
              )
            )
          )
        ),
        1
      ),

    full_join_extra_rows,
      IF(
        join_type_value = "full",
        LET(
          unmatched_mask, ISNA(XMATCH(right_key_vector, left_key_vector)),

          unmatched_right_rows,
            FILTER(right_body_filtered, unmatched_mask, ""),

          unmatched_key_values,
            FILTER(CHOOSECOLS(right_body, right_key_positions), unmatched_mask, ""),

          unmatched_count,
            IF(
              AND(
                ROWS(unmatched_right_rows) = 1,
                COLUMNS(unmatched_right_rows) = 1,
                unmatched_right_rows = ""
              ),
              0,
              ROWS(unmatched_right_rows)
            ),

          reconstructed_left_rows,
            IF(
              unmatched_count > 0,
              MAKEARRAY(
                unmatched_count,
                COLUMNS(left_body),
                LAMBDA(row_index, col_index,
                  LET(
                    key_position_in_left, XMATCH(col_index, left_key_positions),
                    IF(
                      ISNUMBER(key_position_in_left),
                      INDEX(unmatched_key_values, row_index, key_position_in_left),
                      ""
                    )
                  )
                )
              ),
              ""
            ),

          IF(
            unmatched_count > 0,
            HSTACK(reconstructed_left_rows, unmatched_right_rows),
            ""
          )
        ),
        ""
      ),

    IF(
      AND(
        ROWS(full_join_extra_rows) = 1,
        COLUMNS(full_join_extra_rows) = 1,
        full_join_extra_rows = ""
      ),
      VSTACK(output_header, left_join_result),
      VSTACK(output_header, left_join_result, full_join_extra_rows)
    )
  )
)

Excel range to a range literal string

=LAMBDA(_rng,
  LET(
    _fmt,LAMBDA(_x,
      IF(
        ISBLANK(_x),
        """""",
        IF(
          ISTEXT(_x),
          """" & SUBSTITUTE(_x,"""","""""") & """",
          IF(
            ISLOGICAL(_x),
            IF(_x,"TRUE","FALSE"),
            IF(
              ISERROR(_x),
              ERROR.TYPE(_x),
              TEXT(_x,"0.###############")
            )
          )
        )
      )
    ),
    _err,LAMBDA(_n,
      CHOOSE(_n,"#NULL!","#DIV/0!","#VALUE!","#REF!","#NAME?","#NUM!","#N/A","#GETTING_DATA")
    ),
    "{" &
    TEXTJOIN(
      ";",,
      BYROW(
        _rng,
        LAMBDA(_row,
          TEXTJOIN(
            ",",,
            MAP(
              _row,
              LAMBDA(_x,
                LET(_v,_fmt(_x),IF(ISNUMBER(_v),_err(_v),_v))
              )
            )
          )
        )
      )
    ) &
    "}"
  )
)
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment