Want the formula to choose the last data entered cell automatically

[Marsdon]

Hi,

I have question.

There is column with some Variant Values. Based on the last value entered in the column, I have to get the reference automatically.

For eg:
This is the column of values. When I give a reference, I have to get the last entered value in the reference cell.
1
2
3
4
5
6
7
8
9
10
11
12
1
2
3
4
5
6
7
8
9
10
11
12

 

[Xiq]

Hi Marsdon,

Try this:

=INDEX(B:B,MAX((A3:A41=G1)*ROW(A3:A41)))

Where A3:A41 is the range you have the values in
and B:B the column you want to reference to.
The G1 is the value you want to look for.

 

[Marsdon]

Thanks Xiq,

I guess I gave unclear question

Let me explain you further.

A3:A41 is the range where the data. The Last value might be A3 or A4 or A10 or A41. I want the last value that is A3 or A4 or A10 or A41 in the new cell C1. I hope I am clear now.

But Your formula was really useful. =INDEX(B:B,MAX((A3:A41=G1)*ROW(A3:A41))). I can use that in future for any further requirement

 

[AIM]

Enter this formula is C1
=LOOKUP(9.99999999999999E+307,A:A)

 

[Faseeh]

Hi Mardson,

Can you explain with an example and desired result using your data set??? Besides you can also try:

=OFFSET(A1,COUNT(A:A)-1,0)