But in some cases, instead of getting the value, you may want the formula to return the cell address of the value. This could be especially useful if you have a large data set and you want to find out the exact position of the lookup formula result. There are some functions in Excel that designed to do exactly this. In this tutorial, I will show you how you can find and return the cell address instead of the value in Excel using simple formulas.
Lookup And Return Cell Address Using the ADDRESS Function
The ADDRESS function in Excel is meant to exactly this. It takes the row and the column number and gives you the cell address of that specific cell. Below is the syntax of the ADDRESS function: where:
row_num: Row number of the cell for which you want the cell address column_num: Column number of the cell for which you want the address [abs_num]: Optional argument where you can specify whether want the cell reference to be absolute, relative, or mixed. [a1]: Optional argument where you can specify whether you want the reference in the R1C1 style or A1 style [sheet_text]: Optional argument where you can specify whether you want to add the sheet name along with the cell address or not
Now, let’s take an example and see how this works. Suppose there is a dataset as shown below, where I have the Employee id, their name, and their department, and I want to quickly know the cell address that contains the department for employee id KR256.
Below is the formula that will do this:
In the above formula, I have used the MATCH function to find out the row number that contains the given employee id. And since the department is in column C, I have used 3 as the second argument. This formula works great, but it has one drawback – it won’t work if you add the row above the dataset or a column to the left of the dataset. This is because when I specify the second argument (the column number) as 3, it’s hard-coded and won’t change. In case I add any column to the left of the dataset, the formula would count 3 columns from the beginning of the worksheet and not from the beginning of the dataset. So, if you have a fixed dataset and need a simple formula, this will work fine. But if you need this to be more fool-proof, use the one covered in the next section.
Lookup And Return Cell Address Using the CELL Function
While the ADDRESS function was made specifically to give you the cell reference of the specified row and column number, there is another function that also does this. It’s called the CELL function (and it can give you a lot more information about the cell than the ADDRESS function). Below is the syntax of the CELL function: where:
info_type: the information about the cell you want. This could be the address, the column number, the file name, etc. [reference]: Optional argument where you can specify the cell reference for which you need the cell information.
Now, let’s see an example where you can use this function to look up and get the cell reference. Suppose you have a dataset as shown below, and you want to quickly know the cell address that contains the department for employee id KR256.
Below is the formula that will do this:
The above formula is quite straightforward. I have used the INDEX formula as the second argument to get the department for the employee id KR256. And then simply wrapped it within the CELL function and asked it to return the cell address of this value that I get from the INDEX formula. Now here is the secret to why it works – the INDEX formula returns the lookup value when you give it all the necessary arguments. But at the same time, it would also return the cell reference of that resulting cell. In our example, the INDEX formula returns “Sales” as the resulting value, but at the same time, you can also use it to give you the cell reference of that value instead of the value itself. Normally, when you enter the INDEX formula in a cell, it returns the value because that is what it’s expected to do. But in scenarios where a cell reference is required, the INDEX formula will give you the cell reference. In this example, that’s exactly what it does. And the best part about using this formula is that it is not tied to the first cell in the worksheet. This means that you can select any data set (which could be anywhere in the worksheet), use the INDEX formula to do a regular look up and it would still give you the correct address. And if you insert an additional row or column, the formula would adjust accordingly to give you the correct cell address. So these are two simple formulas that you can use to look up and find and return the cell address instead of the value in Excel. I hope you found this tutorial useful. Other Excel tutorials you may also like:
Lookup and Return Values in an Entire Row/Column in Excel Find the Last Occurrence of a Lookup Value a List in Excel Lookup the Second, the Third, or the Nth Value in Excel How to Reference Another Sheet or Workbook in Excel Lookup and Return Values in an Entire Row/Column in Excel
