• 408K Members
• 8,837 Online
• 464K Conversations

New Contributor

# Using Lookup, Index or Match for a Simple Problem

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

3 Replies

# Re: Using Lookup, Index or Match for a Simple Problem

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))

# Re: Using Lookup, Index or Match for a Simple Problem

Thank you Twifoo!

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

nytwodees

# Re: Using Lookup, Index or Match for a Simple Problem

Whenever possible, avoid volatile functions, such as INDIRECT and OFFSET, because they require frequent recalculation.
Anyway, I am pleased to have helped you.
Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
16 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
cntvertex in Discussions on
11 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies