Skip to content
xlsoffice. All Rights Reserved
  • Home
  • Excel For Beginners
  • Excel Intermediate
  • Advanced Excel For Experts

Lookup and Reference Examples

  • How to get last row in mixed data with blanks in Excel
  • Complete List of Excel Lookup and Reference Functions, References and Examples
  • Extract all partial matches in Excel
  • Create hyperlink with VLOOKUP in Excel
  • How to create dynamic named range with OFFSET in Excel

Data Analysis Examples

  • How to create a Histogram in Excel
  • Understanding Pivot Tables in Excel
  • What-If Analysis: Scenarios and Goal Seek in Excel
  • How to calculate average last N values in a table in Excel
  • Conditional Formatting Rules in Excel

Data Validation Examples

  • Excel Data validation exists in list
  • How To Create Drop-down List in Excel
  • Excel Data validation allow uppercase only
  • Excel Data validation must contain specific text
  • Excel Data validation number multiple 100

How to get address of first cell in range in Excel

by

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:

Worked Example:   Merge tables with VLOOKUP in Excel
=ADDRESS(ROW(data),COLUMN(data))

How this formula works

Working from the inside out, the ROW function returns an array of absolute* row numbers for a given range, and the COLUMN function returns an array of column numbers.

The ADDRESS function then assembles and returns an array of addresses. When entered in a single cell, just the item from the array is displayed, which is the address corresponding to the first cell in a range.

Worked Example:   Index and match on multiple columns in Excel

* By absolute, I mean numbers that track to the entire worksheet, not numbers relative to the range itself.

CELL function alternative

Note that the the CELL function can be volatile. If you add it to a worksheet, you’ll notice that you are prompted to save even when you’ve made no changes.

Worked Example:   Check if range contains a value not in another range in Excel

You can also use the CELL function to get the address of the first cell in a range with this formula:

=CELL("address",data)

In this case, we just pass the range into the CELL function with “address”, which returns the address of the first cell in the range.

Post navigation

Previous Post:

How to create dynamic named range with INDEX in Excel

Next Post:

How to create dynamic named range with OFFSET in Excel

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Learn Basic Excel

Ribbon
Workbook
Worksheets
Format Cells
Find & Select
Sort & Filter
Templates
Print
Share
Protect
Keyboard Shortcuts

Categories

  • Charts
  • Data Analysis
  • Data Validation
  • Excel Functions
    • Cube Functions
    • Database Functions
    • Date and Time Functions
    • Engineering Functions
    • Financial Functions
    • Information Functions
    • Logical Functions
    • Lookup and Reference Functions
    • Math and Trig Functions
    • Statistical Functions
    • Text Functions
    • Web Functions
  • Excel VBA
  • Excel Video Tutorials
  • Formatting
  • Grouping
  • Others
  • RIGHT, RIGHTB functions: Description, Usage, Syntax, Examples and Explanation
  • PROPER function: Description, Usage, Syntax, Examples and Explanation
  • How to get top level domain (TLD) in Excel
  • CHAR function: Description, Usage, Syntax, Examples and Explanation
  • TRIM function: Description, Usage, Syntax, Examples and Explanation
  • Next biweekly payday from date in Excel
  • Calculate days remaining in Excel
  • Get first day of previous month in Excel
  • WEEKDAY function: Description, Usage, Syntax, Examples and Explanation
  • EDATE function: Description, Usage, Syntax, Examples and Explanation
  • Calculate cumulative loan interest in Excel
  • MIRR function: Description, Usage, Syntax, Examples and Explanation
  • CUMIPMT function: Description, Usage, Syntax, Examples and Explanation
  • COUPNCD function: Description, Usage, Syntax, Examples and Explanation
  • IPMT function: Description, Usage, Syntax, Examples and Explanation
Acronyms, Abbreviations, Initialism & What They Stand For
© 2021 xlsoffice. All Rights Reserved | Teal Smiles