Lookup for the Cell Address Instead of the Value in Excel (Formula)

In Excel, you can look up a cell, and instead of the value from the cell, you can have its cell address. Let’s say you have the matched value in cell B12. With the formula, you can have the address B12, not the value from it.

This tutorial will look at the possible ways to write a formula for this.

Get Cell Address Instead of Value (CELL + INDEX + MATCH)

Please use the below steps to write this formula:

1. First, enter the CELL function in a cell, and in the first argument specify the “address” as info_type.
   
2. After that, in the second argument, enter the INDEX function.
   
3. Next, in the array argument of the INDEX, refer to the name of names that you have in column A. The range will be A2:A1001.
   
4. Now in the second argument of the INDEX, enter the MATCH function.
   
5. From here, in the MATCH, specify the lookup value in the lookup_value argument, and refer to the range A2:A1001 again in the lookup_array argument. And use 0 in the [match_type] argument to perform an exact match.
   
6. In the end, enter the closing parentheses and hit enter to get the result.

When you hit enter, it will return the cell address of the value you have looked for.

The name “Alec Wright” is in cell A5, and that’s what you have in the result.

=CELL("address",INDEX(A2:A1001,MATCH(C1,A2:A1001,0)))

How Does this Formula Work?

To understand this formula, you need to split it into three parts, as we have used three functions.

In the first part, we have the MATCH function that matches the lookup value from the name of the cells A2:A1001 and returns the position number in the result.

After that, INDEX takes that position number from the MACTH and gets the value from the range in the 4^th position. That means cell A5.

SECRET: INDEX returns the value from the 4th position or cell A5 from the range in the above example. But INDEX, as a function, returns two different values. One is the value from the cell (which you can see above), and the second is the cell’s address where the value is stored (A5). You can’t see or get the cell address as a user, but the CELL function is smart enough to get and use it.

In the end, the CELL function returns the cell address using absolute reference in the result.

If you want to remove the dollar signs from the cell address, you can use the SUBSTITUTE.

=SUBSTITUTE(CELL("address",INDEX(A2:A1001,MATCH(C1,A2:A1001,0))),"$","")

Get the Cell Address by using ADDRESS + MATCH

There’s one more way to write a formula to get the cell address.

=ADDRESS(MATCH(C1,A1:A1001,0),1)

This formula work in two parts: In the first part, we have the MATCH function to get the position number of the cell where you have the lookup value.

After that, in the ADDRESS function, you have the number returned by the MATCH (in the first argument), which is the ROW argument to define the row number.

And in the second argument, we have the column number.  

Row 5 and Column 1 create a cell address A5.

Recommend: 1^st Method