ADDRESS Functions for Excel

Work in Progress :: Please contact us to report errors, typos, etc.

The term "Address" is used to refer to the unique text-based location of a cell or range. "A1" and "$B2" and "$C$31" are all addresses. On the other hand, a "Reference" means that a function is using a non-text reference to a cell or range, such as =SUM(A1,C5).

Ultimately, the collection of LAMBDA functions on this page were designed to allow more flexibility working with addresses for array manipulation. My own need for these functions have come through some simulation work that has required avoiding volatile functions such as INDIRECT and OFFSET. You can read my article on volatile functions if "volatile" is a new term for you.

The other purpose for these functions is their usefulness in solving Excel Esport cases. Most of the Esport competitors have at least a few key lambda functions and macros for dealing with the "map" cases, and so this page and the Map Functions page are devoted to some of these types of functions.

Warning

Some functions like OFFSET, COUNTIF and others require a reference as an input. However, most of the functions on this page do NOT return references. For example, unlike INDEX, IndexByAddress returns only the value or an array of values, so the resulting array cannot be used directly by COUNTIF. You would need to output the results to a cell first, and then use COUNTIF. Or use logical operations like SUM(1*(result="x")) for something like COUNTIF(result, "=x").

Address Functions on This Page

AddressToRow("A1") and AddressToCol :: Replacement for ROW(INDIRECT("A1")) without being volatile.
GetAddressesByValue(value, map_array) :: Returns an array like {"B2";"C3";"D5"} for the locations that match the given value.
AD(cell_or_range) :: A shortcut version of ADDRESS with a range (not text) input.
ToGlobalAddress and ToLocalAddress(address,ref_array) :: Converts addresses from global to local (and vice versa) based on the location of ref_array.
IndexByAddress(array,"A1") :: Returns the value(s) from an array based on the given address(es).
LocalAddress(array) :: A way to get an array of "A1" type addresses relative to the upper-left cell in the array.
OffsetByAddress("B5",rows,cols) :: Similar to OFFSET but based on addresses instead of references, and is not volatile.

Map Functions

Distance("A1","B3",[allow_diagonals]) :: Returns the distance between the from and to addresses using 4- or 8- directions.
DistanceFrom(map_array,valid_value,[allow_diagonals],...) :: Calculates distances from each location in map_array to locations within the map containing the valid_value.
GetAdjAddresses(address,map_array,[valid_value],[mask]) :: Create a 2-column Row Offsets | Column Offsets array based on the mask type.
PathByDirection(start_address,[direction],[num],...) :: This is for the "Go 3 spaces in this direction" type of challenge.
MASK(mask_type,[size],[include_anchor]) :: Useful for creating offsets. Page includes other related functions.

Existing ADDRESS-related functions in Excel

ADDRESS(row_num,col_num,[abs_num],[a1],[sheet_text]) :: Return a text-based address like $A$5 or B2.
INDIRECT(ref_text,[a1]) :: Converts an address like "$C$5" or "Sheet1!C5" into an actual reference to that cell.

=ADDRESS(5,3)
Result: $C$5

=ADDRESS(5,3,4,,"Sheet1")
Result: Sheet1!C5

=INDEX(array,rows,columns)
Result: You should know this one. One of the most useful functions in Excel.

(Volatile) =INDIRECT($C$5)
Result: Returns the value in cell C5 of the current sheet

(Volatile) =OFFSET($C$5,rows,columns,width,height)
Result: Returns the range starting +rows and +columns away from $C$5 that with dimentions width and height

AddressToRow() and AddressToCol()

=AddressToRow(address_text)
=AddressToCol(address_text)

Use these functions to extract the row or column number from a cell address like "B12" or "AB45" without using volatile functions like INDIRECT. They work independently to extract either the row or column.

Yes, using INDIRECT("A1") is both simpler AND slightly faster. So, the main reason to use these lambdas is to avoid using a volatile method (if that's important to you). The AddressToRow and AddressToCol functions use REGEXEXTRACT.

These two functions are building blocks used within some of my other functions, like IndexByAddress.

Example: Extract row numbers from an array of addresses

This example demonstrates how AddressToRow extracts the row numbers from an array of cell addresses.

=AddressToRow({"B4","$U2";"$M$80","AM$20"})

Result: { 4, 2;
         80, 20}

/**
 * Get the Row number from a text address like "BZ45" without INDIRECT.
 * Not for range references (A1:B5) or R1C1 References.
 */
/*
 * Inputs:
 *   address_text: The cell address in text format (e.g., "B12" or "$B$12").
 *                 Do not include the sheet name.
 *                 Can be an array of cell addresses.
 *
 *   Warning: Excel Bug, you sometimes need to wrap with VALUE(AddressToCol(address_text))
 *         when used within other functions.
 */
AddressToRow = LAMBDA(address_text,
LET(doc,"https://www.vertex42.com/lambda/address-functions.html",
    version,"1/10/2025 - Original",
    VALUE(REGEXEXTRACT(address_text, "[0-9]+"))
));

/**
 * Get the Column number from a text address like "BZ45" without INDIRECT.
 * Not for range references (A1:B5) or R1C1 References.
 */
/*
 * Inputs:
 *   address_text: The cell address in text format (e.g., "B12" or "$B$12").
 *                 Do not include the sheet name.
 *                 Can be an array of cell addresses.
 *
 *   Warning: Excel Bug, you sometimes need to wrap with VALUE(AddressToCol(address_text))
 *         when used within other functions.
 */
AddressToCol = LAMBDA(address_text,
LET(doc,"https://www.vertex42.com/lambda/address-functions.html",
    version,"1/10/2025 - Original",
    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
));

GetAddressesByValue()

=GetAddressesByValue(value, map_array, [abs_num], [local_addresses])

Use this function to return an array of addresses to specific items matching value in a grid or map structure.

Example: Get the locations of the rock "🪨" symbols

This example is based on the "Vlookie the Gold Miner" case from the 2023 MEWC qualifying rounds (but this function may not be needed, it's just a fun example of getting local address locations).

=LET(
    array,
    {"🪨","🩸","🌡️";
    "🧱","🔋","🪨";
    "🔩","🧱","🔋";
    "🪨","🧲","🧱"},

    GetAddressesByValue("🪨", array, 4)
)
Result: {"A1";"C2";A4"}

/**
* Get the cell addresses of a specific value from within an array.
*   By default, addresses are relative with the upper-left cell being A1.
*/
/*
* Inputs:
*   value: The target value to find within the array.
*   map_array: The array or range to search.
*   [abs_num]: Optional. Address style (e.g., 1=absolute, 4=relative). Defaults to 4.
*   [local_addresses]: Optional. Boolean. If TRUE, returns local addresses. Defaults to TRUE.
*
* Outputs:
*   Returns an array of cell addresses matching the specified value or "Not Found".
* Requires: LocalAddress
*/
GetAddressesByValue = LAMBDA(value, map_array, [abs_num], [local_addresses],
LET(doc,"https://www.vertex42.com/lambda/address-functions.html",
    version,"1/18/2026 - Updated LocalAddress",
    abs_num, IF(ISOMITTED(abs_num), 4, abs_num),
    local_addresses, IF(ISOMITTED(local_addresses), TRUE, local_addresses),
    addresses, IF(local_addresses,
        TOCOL(LocalAddress(map_array, abs_num)),
        TOCOL(ADDRESS(ROW(map_array), COLUMN(map_array), abs_num))
    ),
    FILTER(addresses, TOCOL(map_array) = value, "Not Found")
));

AD()

=AD(cell, [abs_num], [a1_r1c1], [include_sheetname])

Use this function as a shortcut version of ADDRESS to return the address of a cell using a range as input rather than row and column numbers. Includes the same options as ADDRESS for absolute/relative references and including the sheet name.

Example: Return the addresses for a cell or range

=AD(B2,1,,TRUE)
Result: "Sheet1!$B$2"

=AD(A1:D3,4)
Result:
  {"A1","B1","C1","D1";
   "A2","B2","C2","D2";
   "A3","B3","C3","D3"}

/**
* Shortcut version of the ADDRESS function. Uses cell_or_range as the input.
*/
/*
* Inputs:
*   cell_or_range: The reference cell or range for which to return the address(es).
*   [abs_num]: Optional. Determines the address style (e.g., 1=absolute, 4=relative). Defaults to 1.
*   [a1_r1c1]: Optional. Determines the reference style (1=A1, 0=R1C1). Defaults to 1.
*   [include_sheetname]: Optional. If TRUE, includes the sheet name. Defaults to FALSE.
*
* Outputs:
*   Returns the address of the specified cell, optionally including the sheet name.
*/
AD = LAMBDA(cell_or_range, [abs_num], [a1_r1c1], [include_sheetname],
LET(doc,"https://www.vertex42.com/lambda/address-functions.html",
    version,"1/10/2025 - Original",
    cell, cell_or_range,
    abs_num, IF(ISOMITTED(abs_num), 1, abs_num),
    a1_r1c1, IF(ISOMITTED(a1_r1c1), 1, a1_r1c1),
    include_sheetname, IF(ISOMITTED(include_sheetname), FALSE, include_sheetname),

    IF(include_sheetname,
        ADDRESS(ROW(cell), COLUMN(cell), abs_num, a1_r1c1, TEXTAFTER(CELL("filename", cell), "]")),
        ADDRESS(ROW(cell), COLUMN(cell), abs_num, a1_r1c1)
    )
));

LocalAddress()

=LocalAddress(array, [abs_num])

This is a very useful function for quickly generating text-based addresses of the form A1 or $A$1 for every cell in a specified range or array, relative to the upper-left cell of the array, meaning that the upper-left cell is "A1" regardless of its global location in the worksheet.

Example: Generate relative addresses for a 3x3 grid

This example shows how LocalAddress can generate addresses for an array, not just a range reference.

=LET(
    array,
    {"🪨","🩸","🌡️";
    "🧱","🔋","🪨";
    "🔩","🧱","🔋";},
    LocalAddress(array, 4)
)
Result: {"A1","B1","C1";
         "A2","B2","C2";
         "A3","B3","C3"}         
         
=LocalAddress(D5:F7, 4)
Result: {"A1","B1","C1";
         "A2","B2","C2";
         "A3","B3","C3"}

/**
 * Return an array of addresses relative to the upper-left corner of the array.
 * Output is the same size as the original array.
 */
/*
 * Inputs:
 *   array: The range for which to generate addresses.
 *   [abs_num]: Optional. Address style (e.g., 1=absolute, 4=relative). Defaults to 4.
 */
LocalAddress = LAMBDA(array, [abs_num],
LET(doc,"https://www.vertex42.com/lambda/address-functions.html",
    version, "1/19/2026 - Renamed from RelativeAddress to LocalAddress",
    abs_num, IF(ISOMITTED(abs_num), 4, abs_num),
    ADDRESS( SEQUENCE(ROWS(array)), SEQUENCE(1, COLUMNS(array)), abs_num )
));

ToLocalAddress() and ToGlobalAddress()

=ToLocalAddress(address, ref_array)
=ToGlobalAddress(address, ref_array)

Some functions like IndexByAddress and OffsetByAddress use local addresses for an array where the upper-left cell of the array is A1. If the array is located elsewhere, such as D5:P17, and you are given a global location "D5", you can convert "D5" to the local address "A1" using ToLocalAddress("D5",D5:P17). Likewise, you can convert the relative address "A1" to the absolute address "D5" using ToGlobalAddress("A1",D5:P17).

/**
* Convert global addresses to local addresses based an array location
* Example: array = D5:P17 (anchor at D5)
*          ToLocalAddress("D5",D5:P17) -> "A1"
*/
ToLocalAddress = LAMBDA(address,ref_array,
LET(doc,"https://www.vertex42.com/lambda/address-functions.html",
    aR,AddressToRow(address),
    aC,AddressToCol(address),
    anchor_r,ROW(INDEX(ref_array,1,1)),
    anchor_c,COLUMN(INDEX(ref_array,1,1)),
    ADDRESS(aR-(anchor_r-1),aC-(anchor_c-1),4)
));
/**
* Convert local addresses to global addresses based an array location
* Example: array = D5:P17 (anchor at D5)
*          ToGlobalAddress("A1",D5:P17) -> "D5"
*/
ToGlobalAddress = LAMBDA(address,ref_array,
LET(doc,"https://www.vertex42.com/lambda/address-functions.html",
    aR,AddressToRow(address),
    aC,AddressToCol(address),
    anchor_r,ROW(INDEX(ref_array,1,1)),
    anchor_c,COLUMN(INDEX(ref_array,1,1)),
    ADDRESS(aR+(anchor_r-1),aC+(anchor_c-1),4)
));

IndexByAddress()

=IndexByAddress(array, address)

Use IndexByAddress to retrieve a value from an array using a cell address in text format, such as "B12".

IMPORTANT: The address text is relative to the upper-left cell in the range, so =IndexByAddress(range,"A1") is the same as =INDEX(range,1,1), at least in the sense of returning the value of the first element in the array or range.

IndexByAddress uses AddressToRow and AddressToCol to process the address and avoid volatility (avoids using INDIRECT).

Example: Retrieve a value using a text address

This example shows how IndexByAddress retrieves the value in position "C3" from an array instead of using INDEX(array,3,3)

=LET(
    array,
    {"🪨","🩸","🌡️";
    "🧱","🔋","🪨";
    "🔩","🧱","🔋";},
    IndexByAddress(array, "C3")
)

Result: "🔋"

/**
 * Retrieve a value from an array using a cell address, without INDIRECT.
 * Does not work with range addresses (A1:B5) or R1C1 addresses.
 */
/*
 * Inputs:
 *   array: The array or range of values.
 *   address: The cell address in text format (e.g., "B12" or "$B$12").
 *            Do not include the sheet name.
 *            May be an array of addresses.
 * Requires: AddressToRow, AddressToCol
 */
IndexByAddress = LAMBDA(array, address,
LET(doc,"https://www.vertex42.com/lambda/address-functions.html",
    version,"1/10/2025 - Original",
    INDEX( array, AddressToRow(address), AddressToCol(address) )
));

OffsetByAddress()

=OffsetByAddress(start_address, rows, cols, [width], [height])

If we are going to replace INDEX with IndexByAddress, then it is only right that we also replace OFFSET with OffsetByAddress. Besides being able to do really cool things, OffsetByAddress is not volatile.

I've purposefully avoided doing error-handling with this function, because I want it to produce expected results when the offset addresses are out of range. It returns #VALUE errors for invalid addresses.

Example: Create an array of addresses offset from "B5".

=OffsetByAddress("B5",1,1,4,3)
Result:
   {"C6","D6","E6","F6";
    "C7","D7","E7","F7";
    "C8","D8","E8","F8"}

Example: Why OffsetByAddress is Cool!

When you combine both OffsetByAddress and IndexByAddress, you have a way of working directly with an array. No need for that array to be contained within cells in the spreadsheet.

=LET(
    array,
    {"🪨","🩸","🌡️";
    "🧱","🔋","🪨";
    "🔩","🧱","🔋"},
    IndexByAddress(array, OffsetByAddress( "C3", -1, 0 ) )
)

Result: "🪨"

Example: Yes, it can handle an array of addresses!

When start_address is an array, the width and height parameters are ignored and offset is applied to each address in the array separately resulting in an array the same size as the input start_address array.

=OffsetByAddress({"B5","C2";"D3","E5"},-1,0)
Result:
   {"B4","C1";
    "D2","E4"}

Example: Handles Offset Vectors

When start_address is a single address and row_offset and col_offset are both arrays (same size), then OffsetByAddress returns the corresponding addresses for each row|col pair. If height or width are not 1, then it returns an array of thunks (resulting in #VALUE).

=OffsetByAddress("B2",{0;-1;1},{0;1;1})
Result:
   {"B2";"C1";"C3"}

You can build row and column offset vectors using the Mask function.

▼ Show OffsetByAddress()

/**
 * OffsetByAddress - Generate a range of cell addresses offset from a starting address.
 */
/*
 * Inputs:
 *   start_address: The starting cell address (e.g., "B2") or array of addresses.
 *   row_offset(s): Row offsets relative to the start_address
 *   col_offset(s): Col offsets relative to the start_address
 *   [height]: Optional. The height of the window in rows. Defaults to 1.
 *   [width]: Optional. The width of the window in columns. Defaults to 1.
 * Outputs:
 *   Returns an array of cell addresses corresponding to the specified range.
 *   Will return #VALUE errors for invalid addresses, such as OFFSET("A1",-1,-1)
 * Notes:
 *   - Uses `AddressToRow` and `AddressToCol` to parse the starting address.
 *   - Constructs the resulting addresses using the ADDRESS function.
 *   - Is not a volatile function!
 *   - If start_address is an array, height and width are ignored
 *   - row_offset|col_offset can be equal size arrays. See Mask().
 */
OffsetByAddress = LAMBDA(start_address,row_offset,col_offset,[height],[width],
LET(doc,"https://www.vertex42.com/lambda/address-functions.html",
    version,"1/10/2026 - height|width swapped. Vector behavior for row_offset|col_offset",
    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),
    // Normalize offsets to vectors (scalar -> 1-element vector)
    ro_vec,TOCOL(row_offset),
    co_vec,TOCOL(col_offset),
    ro_is_vec,ROWS(ro_vec)>1,
    co_is_vec,ROWS(co_vec)>1,
    offsets_are_vectors,OR(ro_is_vec,co_is_vec),
    // If one is scalar, keep it scalar but still treat as 1-element vector
    ro_n,ROWS(ro_vec),
    co_n,ROWS(co_vec),
    // Check if need to return thunks
    return_thunks, (width<>1) + (height<>1),
    // Case 1: start_address is an array (ignore width/height)
    final,IF(start_is_array,
        ADDRESS(start_row+row_offset,start_col+col_offset,4),
    // Case 2: start_address is scalar, offsets scalars
    IF(NOT(offsets_are_vectors),
        LET(
        row_vec1, SEQUENCE(height, 1, start_row + row_offset, 1),
        col_vec1, SEQUENCE(1, width, start_col + col_offset, 1),
        row_mat, MMULT(row_vec1, SEQUENCE(1, width, 1, 0)),
        col_mat, MMULT(SEQUENCE(height, 1, 1, 0), col_vec1),
        ADDRESS(row_mat,col_mat,4)
        ),
    // Case 3: start_address is scalar, offsets are vectors
    // addresses (if width=height=1) or thunks otherwise
        MAP(ro_vec,co_vec,LAMBDA(ro,co,
            LET(
                base_r,start_row+ro,
                base_c,start_col+co,
                IF(return_thunks,
                    LAMBDA(
                        LET(
                        r_vec, SEQUENCE(height, 1, base_r, 1),
                        c_vec, SEQUENCE(1, width,  base_c, 1),
                        r_mat, MMULT(r_vec, SEQUENCE(1, width, 1, 0)),
                        c_mat, MMULT(SEQUENCE(height, 1, 1, 0), c_vec),
                        ADDRESS(r_mat, c_mat, 4)
                        )
                    ),
                    ADDRESS(base_r,base_c,4)
                )
            )
        ))
    )),
    final
));

Acknowledgements

I've gathered ideas from a lot of sources. A big thank-you to all the many case creators for Excel Esports, especially Andrew Grigolyunovich, Harry Seiders, Harry Gross, Nick Boberg and the other "map makers"! I wouldn't need these functions if it weren't for you. :-)

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.

Address Functions (Lambdas)