ADDRESS function in Excel

Excel comes with multiple Lookup and Reference functions that let you find matching values. The Lookup and Reference functions help you to work with arrays of data, and are particularly useful when you need to cross reference between different data sets

How to get address of last cell in range in Excel

To get the address of the first cell in a named range, you can use the ADDRESS function together with ROW and COLUMN functions. Formula =ADDRESS(ROW(range)+ROWS(range)-1,COLUMN(range)+COLUMNS(range)-1) Explanation In the example shown, the formula in G6 is: =ADDRESS(ROW(data)+ROWS(data)-1,COLUMN(data)+COLUMNS(data)-1) How this formula works The ADDRESS function builds an address based on a row and column number. Working… read more »

How to get address of named range in Excel

To get the full address of a named range with an Excel formula, you can use the ADDRESS function together with the ROW and COLUMN functions. Formula =ADDRESS(ROW(nr),COLUMN(nr))&”:”&ADDRESS(ROW(nr)+ROWS(nr)-1,COLUMN(nr)+COLUMNS(nr)-1) Explanation In the example shown, the formula in G5 is: =ADDRESS(ROW(data),COLUMN(data),4)&”:”&ADDRESS(ROW(data)+ROWS(data)-1, COLUMN(data)+COLUMNS(data)-1,4) where “data” is the named range B5:D10 How this formula works The core of this formula is… read more »

How to get address of first cell in range in Excel

To get the address of the first cell in a named range, you can use the ADDRESS function together with the ROW and COLUMN functions. See example below; Note: The ADDRESS function builds an address based on a row and column number. Formula =ADDRESS(ROW(range),COLUMN(range)) Explanation In the example shown, the formula in G5 is: =ADDRESS(ROW(data),COLUMN(data))… read more »

Get address of lookup result in Excel

This tutorial shows how to Get address of lookup result in Excel using the example below; Formula =CELL(“address”,INDEX(range,row,col)) Explanation To get the address of a lookup result derived with INDEX, you can use the CELL function. In the example shown, the formula in cell G8 is: =CELL(“address”,INDEX(B5:D11,MATCH(G6,B5:B11,0),2)) Which returns an address of $C$8, the address… read more »

How to use Excel ADDRESS Function

This Excel tutorial explains how to use the ADDRESS function with syntax and examples. Excel ADDRESS function Description The Microsoft Excel ADDRESS function returns a text representation of a cell address. The ADDRESS function is a built-in function in Excel that is categorized as a Lookup/Reference Function. It can be used as a worksheet function in Excel. As… read more »

Excel MAX, MATCH and ADDRESS function illustration

Locate Maximum Value in Excel This example teaches you how to find the cell address of the maximum value in a column. 1. First, we use the MAX function to find the maximum value in column A. 2. Second, we use the MATCH function to find the row number of the maximum value. Explanation: the MATCH function reduces to… read more »

Sidebar