Formula to pick last value in a column

**jayschr** · 03-23-2009, 01:33 PM

Is there a formula or function that allows me to always select the lowest (not min) actual lowest in a column? For example, I have a formula in A1 and I need it part of it to always reference the bottom of a certain range (i.e. A5.A200). So one day the lowest would be A7 but a week later it could be A8. I don't want to have to keep changing the formula in A1 to find the lowest value. I will be adding data on a regular basis. This reads fairly confusing but hopefully someone knows how to help me out! Thanks.

**NBVC** · 03-23-2009, 01:36 PM

Try:

=A5:Index(A:A,Match(9.99999999999e+307,A:A))

**jayschr** · 03-23-2009, 01:51 PM

That formula creates a circular reference. I am a fairly new user to excel so I haven't used the index and match command before. I copied that formula into A1 and entered a value in A5, A1 reflected that value. Leaving that value in A5, I added a new value in A7 and my A1 displayed #VALUE!. Am I doing something wrong?

**NBVC** · 03-23-2009, 01:54 PM

Well, I am not sure what you really want to do...

That formula is meant to be embedded in another formula that refers to the "dynamic range". It doesn't really work on it's own like that.

Do you have a formula where you wanted to replace the range A5:A200 with a dynamic range? Or are you just looking to get the number in the last row? or something else?

**jayschr** · 03-23-2009, 01:55 PM

Ahh I see where I made it more confusing then it needed to be. I just want to pick the last row of valid data entered into a range.

**NBVC** · 03-23-2009, 02:01 PM

So if you want the actual address of the row:

=ADDRESS(MATCH(9.99999999E+307,A5:A200)+ROW(A5)-1,1)

if you want the value in that row,

=LOOKUP(9.99999999E+307,A5:A200)