How to Get the Cell Address Instead Of Value In Excel?

by

In Excel, we often use formulas like INDEX/MATCH, VLOOKUP, and XLOOKUP to match the lookup value but in some scenarios, we came across a situation in which we lookup for the address of the cell containing that value instead of the value itself.

This is useful especially when we have a large amount of data and we want the exact address of the cell containing some specific value.

In this tutorial, I will show you how you can effectively get the cell address of the cell containing the lookup value.

There are some built-in functions in Excel using which we can effectively and easily find the address of a cell instead of the value in Excel.

Using ADDRESS Function to Return the Cell Address

The ADDRESS is an Excel built-in function that provides us with the location of a specific cell based on the Row number and Column number.

It gives us both absolute Address and relative Address depending on our requirements. 

= Address(row_num, col_num, [abs_num], [a1], [sheet_text])

Where

  • row_num is the Required field: it specifies the row whose address we want to locate.
  • col_num is also a Required field:  it specifies the column whose address we want to locate.
  • [abs_num]: is an optional parameter that provides the absolute address relative address or mixed address depending on the value. The default value would be 1 (Absolute Address)
  • [a1]:  this is also an optional parameter that specifies whether we want the address in A1 referencing style or R1C1 referencing style.
  • [sheet-text]: If we want to specify the sheet name in which we are looking for the address.

In any Excel formula all the parameters that are wrapped around by [ ] are optional parameters and others are Required.

Example: Get the Absolute Address of a Lookup Value using the ADDRESS function

Below is the sample data set that we are going to use throughout this article where we have Employee Id, Name, Department, and their salary.

Sales Dataset

So now take a scenario where we want to look for the address of an employee’s salary based on their name i.e our lookup value would be the employee name and we want to get the location of the salary cell instead of value.

 Examine the following case

Getting salary cell address instead of value

Now in this example, I create the dropdown list of Emp_Name and used the following formula in order to match the address of salary.

=ADDRESS(MATCH(G6,B1:B12,0),4)
ADDRESS function to get cell location
ADDRESS function to get cell location

In the above example, I used the ADDRESS function in combination with the Match function to get the location of the value in the Salary column by matching it with the lookup value that the employee’s Name

Let us have a quick look at the match function and it will make everything clear. Here is the syntax of the match function

MATCH (lookup_value, lookup_array, [match_type])

In the above example, the look_up value is cell G6 which is highlighted with green color, lookup_array is B1:B12 which is highlighted with yellow color and the matching value location is located in the Salary column which is highlighted with orange. 

The match function locates the row number of the cell containing the lookup value i.e Emp_Name

As the Salary is in column four that’s why we specify parameter 4 as the [col_num] in the ADDRESS function.

Cell address as absolute reference

It can be observed that we get the correct address of William which is $D$8

Example: Get the Relative Address of the Lookup Value using the ADDRESS function

We can get the relative cell reference address of value by a little modification in the above formula.

Simply assign the [abs_num] = 4 in the ADDRESS formula and we get the relative address as shown below.

Formula to get cell address as relative

Here is the output

cell address as relative
Also read: Excel Showing Formula Instead of Result (How to FIX!)

Cell Function to Match and Return the Cell Address

Cell function is also a built-in function in excel that returns information about the cell i.e address, formatting, and content of the cell.

But in this tutorial, we only cover how to get the location of a cell using Cell Function. 

Here is the syntax of the CELL function

= CELL (“info_type”, [reference])

Info_type:  This is a Required parameter that specifies the type of information we are looking for. So, in case we are looking for the address of the cell so input “address”.

[reference]: This is an optional parameter that specifies which cell address we are locating. So select the cell whose address you want to locate. 

Example: Get the Address of Lookup Value using the CELL function

Now let’s look at an example where we use the CELL function to get the address of the reference cell.

For this example, I used the sample Employee data set as shown.

Dataset with salary of employees

Here is the formula we are going to use

CELL("address",INDEX($A$1:$D$12,MATCH($G$6,$B$1:$B$12,0),4))

Here we have used the CELL function in combination with the Index and Match function to get the address of the Salary value based on the lookup Emp_Name value. 

Cell function to get cell address
Cell function to get cell address

We have already discussed the Match function that how it gets the row number now let’s give a brief overview of the index function

Here is the syntax of the Index function

INDEX(array, row_num, [column_num])
INDEX(reference, array, row_num, [column_num])

The index function basically returns the value depending upon the row number and Column number but at the same time, it is also returning the reference of the cell containing that value which can be seen from the formula.

So, here we are using the reference that is returned by the INDEX formula. 

In the Employee example, the array parameter is the entire Employee table.

Row number is calculated by the MATCH function and [column_num] is 4 as we want to match the address of the Salary column. 

The best thing about the CELL function is that you can place your table anywhere in your sheet and adding rows and columns before the dataset won’t affect the results of the formula as in the case of the ADDRESS function.

So, always used the CELL function when you have a large dataset but for fixed small data you can use the ADDRESS function as well.

Moreover, if you have any further queries please leave a comment.

Other Excel articles you may also like:

I am a huge fan of Microsoft Excel and love sharing my knowledge through articles and tutorials. I work as a business analyst and use Microsoft Excel extensively in my daily tasks. My aim is to help you unleash the full potential of Excel and become a data-slaying wizard yourself.

Leave a Comment