How to Return Cell Reference Instead of Value in Excel

Not very often can happen that we need to return the actual reference of a certain cell, instead of the cell value itself. If, however, you happen to need this information, we will explain how to do this in the example below.

Return Cell Reference Instead of Value

For the example, we will use the list of NBA players and points scored in one random night:

A picture containing text

Description automatically generated

Now, let us presume that we want to find out in which exact cell LeBron James is located. For the following formula to work, we will presume that we already know the column number of the thing that we are looking for. We will enter the name of LeBron James in cell E2, and the following formula in cell F2:

This formula is fairly easy. First, we input ADDRESS, which is a basic formula to return the cell reference. To make this formula work, we have to provide row and column numbers, in that order. For row, we use the MATCH formula to match the exact name of Lebron James located in cell E2 with the cell where this value is found in range A1:A7.

We then use the number 1 as a column number, as we said that is a constant. We get the following results:

Graphical user interface, application, table, Excel

Description automatically generated

Now, this is the exact figure that we need. If we would change our constant, i.e. our column number to be any other number, for example, number 3, we would get an incorrect result:

Graphical user interface, application, table, Excel

Description automatically generated

We still get the right number for rows, which is something. However, we need a little tweak to make this formula work for both rows and columns.

To do this, we will input the following formula in cell F3 (we placed desired player- Kyrie Irving in cell E3):

The CELL function returns us information about the formatting, color, type, etc. of a specific cell. The list (not full) of options that we can use is as follows:

Table

Description automatically generated

We will choose an option that is not presented in a list, which is “address”. After that, for a reference of our cell, we will use INDEX and MATCH. Our array for the INDEX formula will be columns A and B. For the rows, we will use MATCH to find the value of our cell in the range A1:A7. For columns, we input just the number 0.

This is the result we end up with:

Graphical user interface, application, table, Excel

Description automatically generated

We get the result that shows cell A4, which is the exact cell where Kyrie Irving is located in our table.

Tomasz Decker is an Excel specialist, skilled in data analysis and financial modeling.