Excel ADDRESS function to get cell address and more

This tutorial provides a concise overview of the Excel ADDRESS function's syntax and demonstrates its use in retrieving cell addresses and more. Manually entering cell references is an alternative, but the ADDRESS function, coupled with other functions, offers solutions when direct cell referencing is impossible.

Key Features Covered:

• Excel ADDRESS function: syntax and basic applications
• Practical examples of ADDRESS function usage:
  • Retrieving cell values using row and column numbers
  • Determining the address of cells containing maximum or minimum values
  • Extracting column letters from column numbers
  • Obtaining the address of named ranges (Excel 2007-2019 and Excel 365/2021)

Excel ADDRESS Function: Syntax and Basic Usage

The ADDRESS function generates a cell address (as text) based on given row and column numbers. It's compatible with Excel versions 2007 and later, including Microsoft 365.

Syntax:

ADDRESS(row_num, column_num, [abs_num], [a1], [sheet_text])

• row_num (required): Row number for the cell reference.
• column_num (required): Column number for the cell reference.
• abs_num (optional): Reference type (1=absolute, 2=relative column/absolute row, 3=absolute column/relative row, 4=relative). Defaults to absolute ($A$1).
• a1 (optional): Reference style (1 or TRUE = A1 style, 0 or FALSE = R1C1 style). Defaults to A1.
• sheet_text (optional): Worksheet name (enclosed in quotes). Omitted for the current sheet.

Examples:

• =ADDRESS(1,1): Returns "$A$1" (absolute reference).
• =ADDRESS(1,1,4): Returns "A1" (relative reference).

The table below illustrates various ADDRESS function outputs:

Formula	Result	Description
=ADDRESS(1,2)	$B$1	Absolute cell reference
=ADDRESS(1,2,4)	B1	Relative cell reference
=ADDRESS(1,2,2)	B$1	Relative column, absolute row
=ADDRESS(1,2,3)	$B1	Absolute column, relative row
=ADDRESS(1,2,1,FALSE)	R1C2	Absolute R1C1 reference
=ADDRESS(1,2,4,FALSE)	R[1]C[2]	Relative R1C1 reference
=ADDRESS(1,2,1,"Sheet2")	Sheet2!$B$1	Absolute reference to another sheet
=ADDRESS(1,2,4,"Sheet2")	Sheet2!B1	Relative reference to another sheet

Practical Applications of the ADDRESS Function

The following examples demonstrate the ADDRESS function's use within more complex formulas:

1. Retrieving a Cell Value Based on Row and Column Numbers:

Combine ADDRESS with the INDIRECT function: =INDIRECT(ADDRESS(E1,E2)) (where E1 contains the row number and E2 the column number).

2. Finding the Address of the Cell with the Highest or Lowest Value:

Use ADDRESS with MAX/MIN and MATCH:

• =ADDRESS(MATCH(MAX(B2:B7),B:B,0),COLUMN(B2)) (for the maximum value)
• =ADDRESS(MATCH(MIN(B2:B7),B:B,0),COLUMN(B2)) (for the minimum value)

3. Obtaining a Column Letter from a Column Number:

Use ADDRESS and SUBSTITUTE: =SUBSTITUTE(ADDRESS(1,A2,4),"1","") (where A2 contains the column number).

4. Getting the Address of a Named Range:

• Excel 2007-2019: Requires separate formulas for the first and last cells, then concatenating them with a colon. See the original document for detailed formulas.
• Excel 365/2021: Leverage implicit intersection (@) to simplify the process. See the original document for detailed formulas.

Downloadable practice workbook available in the original document.

The above is the detailed content of Excel ADDRESS function to get cell address and more. For more information, please follow other related articles on the PHP Chinese website!