Home

Using Lookup, Index or Match for a Simple Problem

%3CLINGO-SUB%20id%3D%22lingo-sub-356029%22%20slang%3D%22en-US%22%3EUsing%20Lookup%2C%20Index%20or%20Match%20for%20a%20Simple%20Problem%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-356029%22%20slang%3D%22en-US%22%3E%3CP%3EI%20am%20using%20Excel%202000%20for%20Windows.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20a%2060-month%20amortization%20table%20having%20the%20following%20columns%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPayment%23%26nbsp%3B%20%26nbsp%3BMonthly%20Payment%26nbsp%3B%20%26nbsp%3BDue%20Date%26nbsp%3B%20%26nbsp%3BPayment%20Date%26nbsp%3B%20%26nbsp%3BPrincipal%26nbsp%3B%20%26nbsp%3BInterest%26nbsp%3B%20%26nbsp%3BPrincipal%20Balance%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThis%20table%20begins%20on%20Row%205%20Column%201.%3C%2FP%3E%3CP%3EThis%20table%20has%20all%20cells%20correctly%20filled%20with%20the%20data%20for%20all%20the%20above%20column%20labels%20EXCEPT%20the%20Payment%20Date.%20The%20Payment%20Date%20cells%20will%20be%20dated%20as%20I%20make%20a%20monthly%20payment.%20The%20remaining%20payment%20dates%20remain%20blank.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EMy%20A2%20cell%20is%20Titled%20%22Current%26nbsp%3BPrincipal%20Balance.%22%20The%20B2%20cell%20will%20contain%20the%20data%20of%20the%20Principal%20Balance%26nbsp%3Bthat%20is%20shown%20in%20the%20Row%20of%20the%20last%20Payment%20Date%20entered.%20After%26nbsp%3Bthe%20payment%26nbsp%3Bdate%26nbsp%3Bhas%20been%20entered%20the%20Principal%20Balance%20is%20shown%20in%20the%20last%20column%20of%20the%20table%20for%20that%20row.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20keys%20are%20to%20determine%20the%20row%20cell%20with%20the%20last%20payment%20date%20entered.%3C%2FP%3E%3CP%3ESecondly%2C%20once%20that%20row%20has%20been%20determined%2C%20the%20principal%20balance%20for%20that%20row%20should%20populate%20the%20B2%20cell%20(the%20Current%20Principal%20Balance).%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20know%20that%20this%20code%20will%20be%20pretty%20simple%20for%20advanced%20Excel%20users.%20Unfortunately%2C%20my%20skill%20level%20does%20not%20permit%20me%20to%20understand%20combining%20functions%26nbsp%3Bproperly.%20Once%20I%20see%20the%20code%2C%20it%20will%20help%20me%20understand%20how%20the%20functions%20work%20together.%26nbsp%3B%20Please%20HELP!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Enytwodees%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-356029%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-358684%22%20slang%3D%22en-US%22%3ERe%3A%20Using%20Lookup%2C%20Index%20or%20Match%20for%20a%20Simple%20Problem%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-358684%22%20slang%3D%22en-US%22%3EWhenever%20possible%2C%20avoid%20volatile%20functions%2C%20such%20as%20INDIRECT%20and%20OFFSET%2C%20because%20they%20require%20frequent%20recalculation.%3CBR%20%2F%3EAnyway%2C%20I%20am%20pleased%20to%20have%20helped%20you.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-358494%22%20slang%3D%22en-US%22%3ERe%3A%20Using%20Lookup%2C%20Index%20or%20Match%20for%20a%20Simple%20Problem%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-358494%22%20slang%3D%22en-US%22%3E%3CP%3EThank%20you%20Twifoo!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYour%20function%20worked%20perfectly.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20figured%20another%20solution.%20However%2C%20my%20method%20is%20not%20as%20elegant%20or%20economical%20as%20yours.%20Here%20it%20is%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ETo%20automatically%20fill-in%20cell%20I8%3C%2FP%3E%3CP%3E%3DSUMPRODUCT(MAX((D11%3AD75%26lt%3B%26gt%3B%22%22)*ROW(D11%3AD75)))%3C%2FP%3E%3CP%3ETo%20automatically%20fill-in%20cell%20H4%3C%2FP%3E%3CP%3E%3DINDIRECT(ADDRESS(I8%2C%26nbsp%3BI9))%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-center%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F84724i377768AB07788A88%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22Sample%20Amortization%20Table.jpg%22%20title%3D%22Sample%20Amortization%20Table.jpg%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Enytwodees%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-357399%22%20slang%3D%22en-US%22%3ERe%3A%20Using%20Lookup%2C%20Index%20or%20Match%20for%20a%20Simple%20Problem%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-357399%22%20slang%3D%22en-US%22%3EAssuming%20your%20Table%20Labels%20are%20in%20A5%3AG5%20and%20Payment%20%23%200%20to%2060%20are%20in%20A6%3AA66%2C%20your%20formula%20in%20B2%20is%3A%3CBR%20%2F%3E%3DINDEX(G6%3AG66%2C%3CBR%20%2F%3ECOUNT(D6%3AD66))%3C%2FLINGO-BODY%3E
nytwodees
New Contributor

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

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

 

Sample Amortization Table.jpg

 

nytwodees

 

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