In this article, we will learn how to retrieve cell address of a lookup value.
There will be times when you would want to get address of cell from which a value is being retrieved. Using that address you can easily retrieve adjacent values using OFFSET function.
Let’s see how you can fetch address of cell of a lookup value.
Any value, retrieved using INDEX function can be tracked using CELL function.
Generic Formula:
“Address”: It is constant in CELL function, for getting cell address.
Rest is just INDEX function. INDEX will retrieve value from array and index number provided.
Let’s see an example.
Example 1
Here, I have a table. I simply want to retrieve address of cell in range A1:A14 at index 5.
To get cell address from INDEX, write this formula.
Example 2: Lookup Cell Address
Many times, you will want to lookup for cell address instead of value in cell. To Lookup a cell value we use INDEX-MATCH or VLOOKUP. To get cell address of lookup value, you need to use INDEX-MATCH.
Here, I am looking up for House No. of Cust. ID 105. I also want to know the cell address of that House No. I simply used INDEX-MATCH function to retrieve the House No. .
Now to get cell address of retrieved value, we will write this CELL formula in J2.
You can see that we have done a lookup of cell address.
So how it works?
As we know, CELL function provides information about the given cell reference. We use “address” as info type to get address of a cell. And for reference we use INDEX function. It might seem that INDEX function returns value, but underneath it actually returns cell reference. So yeah, you can lookup addresses of any value returned by INDEX function.
One question may arrive, what is use of looking up cell address in excel?
One simple and effective use is looking up other adjacent values without using VLOOKUP or INDEX-MATCH. You can easily use OFFSET function to lookup values adjacent to that value.
Like this:
Here, I wanted to get Name using looked up address. So I used OFFSET function with INDIRECT. Similarly, you can look up any value adjacent to a looked up cell, without having to write long lookup formulas. This will be faster an light to.
Tell me how you will use this formula of address lookup. What’s your idea? And if you are stuck somewhere in getting coordinates of looked up cell, feel free to ask your question in the comments section below.
Download file:
Related Articles:
How to use VLOOKUP from Two or More Lookup Tables in Excel
How to use the CELL function in Excel
How to use the ADDRESS Function in Excel
Popular Articles :
50 Excel Shortcut to Increase Your Productivity : Get faster at your task. These 50 shortcuts will make you work even faster on Excel.
How to use the VLOOKUP Function in Excel : This is one of the most used and popular functions of excel that is used to lookup value from different ranges and sheets.
How to use the COUNTIF function in Excel : Count values with conditions using this amazing function. You don't need to filter your data to count specific values. Countif function is essential to prepare your dashboard.
How to use the SUMIF Function in Excel : This is another dashboard essential function. This helps you sum up values on specific conditions.
The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.
