formula to find last value in a list on another worksheet

[Garry T]

Following is an excerpt from a checkbook. I'm on another worksheet or in another workbook, and would like the last value in the checkbook automatically updated. For ex, if I'm on a different worksheet and it's Sept 9th, I would want 37,086.67 to show w/out having to go get it. If I'm on that worksheet today, I would want that cell to read 36,778.24. There is a new line and updated balance every time a transaction is posted. Thank you

38,217.41	9/9/2019
38,079.54	9/9/2019
37,921.09	9/9/2019
37,674.78	9/9/2019
37,411.32	9/9/2019
37,086.67	9/9/2019
35,377.24	9/10/2019
36,355.15	9/11/2019
36,778.24	9/11/2019

<colgroup><col><col></colgroup><tbody>
</tbody>

 

[Joe4]

You can use a reverse LOOKUP, as shown here: https://exceljet.net/how-to-lookup-first-and-last-match

So, if your data is in columns A and B, and the date you are looking up is in cell F1, the formula would look like:

=INDEX(A:A,MATCH(F1,B:B,1))

 

[Garry T]

Thanks for your reply, Joe! I've used this forum several times and I'm never very good at asking my questions. The first picture is cell E2 on sheet1:

Debit (credit)

35,878.24

<tbody>
</tbody>

The next picture is the running balance of my checkbook. I want cell E2 on sheet1 to always show the current (i.e., last balance) on the checkbook.

36,186.67	9/9/2019
34,477.24	9/10/2019
35,455.15	9/11/2019
35,878.24	9/11/2019

<tbody>
</tbody>

notice how the last balance 35,878.24 shows in cell E2 on sheet1, except that right now I'm doing it manually. I guess maybe I would want to enter a formula in cell E2 that finds the last value in the list of checkbook balances.

Thanks again!

 

[Joe4]

Try:

=LOOKUP(2,1/(A:A<>""),A:A)

as shown and explained here: https://exceljet.net/formula/get-value-of-last-non-empty-cell

 

[Garry T]

That worked! Thank you very much, Joe!

I ended up using ISNUMBER after I clicked your explanation link! Thanks again!

 

[Joe4]

You are welcome.