Using Lookup, Index or Match for a Simple Problem

[nytwodees]

I am using Excel 2000 for Windows.

I have a 60-month amortization table having the following columns:

Payment#   Monthly Payment   Due Date   Payment Date   Principal   Interest   Principal Balance

This table begins on Row 5 Column 1.

This table has all cells correctly filled with the data for all the above column labels EXCEPT the Payment Date. The Payment Date cells will be dated as I make a monthly payment. The remaining payment dates remain blank.

My A2 cell is Titled "Current Principal Balance." The B2 cell will contain the data of the Principal Balance that is shown in the Row of the last Payment Date entered. After the payment date has been entered the Principal Balance is shown in the last column of the table for that row.

The keys are to determine the row cell with the last payment date entered.

Secondly, once that row has been determined, the principal balance for that row should populate the B2 cell (the Current Principal Balance).

I know that this code will be pretty simple for advanced Excel users. Unfortunately, my skill level does not permit me to understand combining functions properly. Once I see the code, it will help me understand how the functions work together.  Please HELP!

nytwodees

[#2]

Assuming your Table Labels are in A5:G5 and Payment # 0 to 60 are in A6:A66, your formula in B2 is:
=INDEX(G6:G66,
COUNT(D6:D66))

[#3]

Thank you Twifoo!

Your function worked perfectly.

I figured another solution. However, my method is not as elegant or economical as yours. Here it is:

To automatically fill-in cell I8

=SUMPRODUCT(MAX((D11:D75<>"")*ROW(D11:D75)))

To automatically fill-in cell H4

=INDIRECT(ADDRESS(I8, I9))

nytwodees

[#4]

Whenever possible, avoid volatile functions, such as INDIRECT and OFFSET, because they require frequent recalculation.
Anyway, I am pleased to have helped you.