Another Lookup Question

[Bob]

I have a table of data where the top row contains this month's dates, and the
cell beneath each date contains a bank balance. Can you please help me
create a formula to put in the far right column(directly after the last date
of the month) that will return the value of the second to last bank balance I
have entered.

For example, today (7/24) I entered a balance, so I want the formula to
automatically update with the bank balance of 7/23.

Thanks in advance!

 

[T. Valko]

Assuming all the cells up to today's date contain an entry:

B1:AF1 = dates
B2:AF2 = balance

=INDEX(B2:AF2,MATCH(TODAY(),B1:AF1,0)-1)

 

[Mike H]

Maybe

=INDEX(2:2,LARGE(IF((A2:N2<>"")*(A2:N2<>""),COLUMN(A2:N2)),2))


Returns the second to last value in A2 - N2
Which is an array so submit with Ctrl+Shift+Enter

Mike

 

[T. Valko]

Hmmm...

I just thought of something...

If today is the 1st of the month, then what?