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])
- 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.
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
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.
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.
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.
Here is the output
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.
Here is the formula we are going to use
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.
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: