How to return cell address instead of value in Excel

A cell address can be referred to as a column letter and a row number that identifies a cell on a worksheet. Lookup Formulas in Excel to find the matching value or corresponding value in the same row or column. However, one might need to return to the cell address of the value, especially when working with a large amount of data. The various functions that help in this are as follows.

Using the ADDRESS. Function

The ADDRESS Function in Excel works by taking the row and the column number and giving you the specific cell's cell address. The ADDRESS function is as follows:

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


Whereby;

Row _num is the row number of the cell for which you want the cell address

Column_num is the column number of the cell for which you want the address

[abs _num] is an optional argument where you can specify whether you want the cell reference to be relative, absolute, or mixed

[A1] is an optional argument where you specify whether you want the reference in the R1C1 style or A1

[Sheet_text] is an optional argument where you can specify whether you want to add the sheet name along with the cell address or not.

Note

The ADDRESS Function has a setback in that it can't work if you add the row above the data set or a column to the left of the dataset; if you add any column to the left of the dataset, the formula counts the columns from the beginning of the worksheet and not from the beginning of the data set.

Using the Cell Function

The CELL Function is normally the best as it gives you more information about a cell than the ADDRESS Function. The syntax of the CELL Function is as follows:

=CELL (info_type,[reference])


Whereby;

Info_type is the information about the cell you want. It can be the address, column number, filename.

[reference] is an optional argument where you can specify the cell reference you need the cell information.

Using the INDEX Function

The Excel INDEX Function Returns a value from a table based on the index ( row number and column number). The INDEX Function is a built-in function in Excel and is categorized as a lookup and reference function. The syntax of the INDEX Function is =INDEX( array, row_num,[column_num])


Using the Match Function

The Excel Match Function searches a value in an array and returns the position of that item. The syntax of MATCH Function is =MATCH( lookup_value, lookup_array,[match_type])


Note

The ADDRESS function is the most commonly used Function when returning cell address instead of value in Excel. The ADDRESS Function serves many purposes when used together with other functions. These functions include:

  • Getting cell address from the row and column number
  • Finding cell values from a row and column number
  • Returning address of cell with the highest value
  • Getting column letter based on the column number

The address function only returns the cell address as text. If you need the cell value use the INDIRECT Function and the Address function.