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.
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)
Leave a Reply