How to Get Last Value In Column

007
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:

=CELL(“address”,INDEX(array,row_index_number,[col_index_number]))

“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.
008
To get cell address from INDEX, write this formula.

=CELL(“address”,INDEX(A1:A14,5,0))

009
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.
0010

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.

=CELL(“address”,INDEX(D2:D14,MATCH(H2,A2:A14,0)))

0011
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:
0012
Here, I wanted to get Name using looked up address. So I used OFFSET function with INDIRECT. Similarly, you can lookup any value adjacent to 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.

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

Terms and Conditions of use

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.

Visit Us On TwitterVisit Us On FacebookVisit Us On Google PlusVisit Us On Youtube