get lowest row value in column

**rainman101** · 01-24-2012, 12:19 PM

Hello,

I'd like to know a formula for returning the lowest non-blank value in a column. Imagine a checkbook register, where the balance column will naturally fill down with time. I'd like to see the ending balance, no matter what row it is on, in a cell on a different sheet. I there a way to do this?

**Andrew-R** · 01-24-2012, 12:23 PM

If your balance is in column A on sheet1 then try:

=LOOKUP(REPT("z",255),Sheet1!A:A)

Edited to add: Stupid me, that doesn't work with numeric values.

Instead try:

=INDEX(Sheet1!A:A,MAX(IF(Sheet1A1:A1000<>"",ROW(Sheet1!A1:A1000))),0)

Entered as an array formula, i.e. you must enter it using Ctrl-Shift-Enter, not just enter.

**rainman101** · 01-24-2012, 12:26 PM

Thanks! I'll try this when I get home and let you know how it worked.

**James Gerlach** · 01-24-2012, 12:50 PM

Here's another way:

=INDEX(E4:E12,MATCH(0,E4:E12,-1),0) where E4:E12 is the location of the column of numbers

**Haseeb A** · 01-24-2012, 01:11 PM

This will return the LAST NUMERIC value in column A:A

=LOOKUP(9E300,A:A)

This will return the LAST TEXT value in column A:A

=LOOKUP(REPT("z",255),A:A)

This will return the LAST VALUE (regardless of entry, Number or Text) in column A:A

=INDEX(A:A,MAX(IFERROR(MATCH(REPT("z",255),A:A),1),IFERROR(MATCH(9E300,A:A),1)))

Or a short one with CTRL+SHIFT+ENTER

=INDEX(A:A,MAX(IFERROR(MATCH({"zzzzzzzzzzzzzzz",9E300},A:A),1)))