• 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:

  • Multiple Condition Lookup Formula in Excel
    Multiple Condition Lookup Formula in Excel
  • Import Multiple Excel Files with Multiple Sheets in Excel
    Import Multiple Excel Files with Multiple Sheets in Excel
  • Excel FILTER Function – The Best Function in Excel
    Excel FILTER Function – The Best Function in Excel
  • Create a Case Sensitive Lookup Formula in Excel
    Create a Case Sensitive Lookup Formula in Excel

Reader Interactions

Leave a Reply Cancel reply

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

Primary Sidebar

Popular Posts

  • Excel Fixtures and League Table Generator
  • 5 Reasons Why your Excel Formula is Not Calculating
  • Excel IF Function Contains Text – A Partial Match in a Cell
  • Excel Formula to Display the Sheet Name in a Cell
  • 4 Alternatives to Nested IF Formulas
  • How to Hyperlink to a Hidden Worksheet in Excel
  • How to Create a Reverse FIND Formula
  • Check if a Value is an Even Number
  • Conditional Formatting Multiple Columns – 3 Examples
  • IF Function in Power Query Including Nested IFS

Recent Posts

  • SEQUENCE Function in Excel
  • Sort by Drop Down List in Excel
  • Advanced SUM Function Examples – The Power of SUM
  • Display Negative Time in Excel
  • Lookup Multiple Values in Excel
Advanced Excel Success book

Footer

Contact

[email protected]gaga.com

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 2020 Computergaga · All Rights Reserved ·