• Skip to main content
  • Skip to primary sidebar
  • Skip to footer

Microsoft Excel Tips and Tricks - Computergaga

  • Home
  • Excel Online Courses
  • Excel Functions List
  • Free Downloads
  • Blog
  • Tutorials
  • About
Home / Formulas / Excel Formula to Find the Cell Address of a Value

Excel Formula to Find the Cell Address of a Value

You can use a lookup formula to return the address of a cell instead of a value within an Excel spreadsheet. You would usually be doing this to feed another function with the cell address.

In this example, we will look for a Customer using its ID and return the address of the cell that contains the customer’s country.

Find cell address in a customer list

Using the ADDRESS Function

The Excel ADDRESS function will be used to return the address of the cell when the value is found. The function is written as follows;

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

Row_num: The row number of the cell.

Column_num: The column number of the cell.

Abs_num: The type of reference you want to return. By default, the ADDRESS function will return an absolute reference such as $F$12. The other options are to return an absolute row/relative column, relative row/absolute column or a relative reference. You make your choice by entering a number between 1 and 4.

a1: This is the reference style. Enter 0 for an R1C1 style such as R12C6 (this is cell F6), or enter 1 for an A1 style which is the classic F6. The A1 style is the default.

Sheet_text: The name of a worksheet to use in the reference.

Formula to Find the Cell Address

The Excel ADDRESS function is combined with the MATCH function to find the cell address of a value.

=ADDRESS(MATCH($I$4,$A$1:$A$92,0),6)

The MATCH function is used to look for the row number of the customer. The column number is entered as 6. Another MATCH function could have been used to locate the column number also.

No other arguments have been used meaning the answer will be returned using the defaults of an absolute cell reference, in A1 style and with no sheet text.

The example below shows a 4 being added to the ADDRESS function to return the cell address as a relative cell reference.

=ADDRESS(MATCH($I$4,$A$1:$A$92,0),6,4)

Watch the Video

Related Posts:

  • Copy a formula down in Excel
    How to Copy a Formula Down in Excel
  • Import multiple files thumbnail
    Import Multiple Excel Files with Multiple Sheets in Excel
  • Display negative time thumbnail
    Display Negative Time in Excel
  • Count words in a cell thumbnail
    Count the Number of Words in a Cell

Reader Interactions

Leave a Reply Cancel reply

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

Primary Sidebar

Recent Posts

  • Custom Sort in Excel
  • Case Sensitive Lookup with XLOOKUP in Excel
  • Aggregate Rows in Power Query
  • How to Copy a Formula Down in Excel
  • How to Compare Dates in Excel
Advanced Excel Success book

Footer

Contact

[email protected]

LinkedIn Twitter YouTube

Useful Links

  • Home
  • Excel Online Courses
  • Tutorials
  • Sitemap
  • Privacy Policy
  • Jobs by Jooble

Course Topics

  • Charts
  • Courses
  • Dashboards
  • Downloads
  • Excel Tips
  • Formulas
  • Mail Merge
  • Office
  • PivotTables
  • Power BI
  • Power Pivot
  • Power Query
  • VBA

Join Our Mailing List

© Copyright 2009 Computergaga · All Rights Reserved ·