INDEX MATCH with multiple criteria: solve a finance lookup exercise
Practise INDEX MATCH on a fictional ledger: entity, account and date criteria, a duplicate combination, a missing one and a text-stored date — with worked answers.
THE FINX JOURNAL LEARN BY DOING
Excel for finance
Make formulas behave the way you intend. Work through references, lookups and the small details that make a spreadsheet reliable.
In this topic
THE READING LIST
Remove duplicates in Excel: protect legitimate finance transactions
A payments ledger where look-alike rows are real transactions. Derive the composite key, preview removals with COUNTIFS, and reconcile control totals before anything is deleted.
IRR vs XIRR: choose the return calculation that matches the dates
Apply Excel's IRR and XIRR to the same irregularly dated investment, watch the two answers disagree, and learn which function reads the calendar and which only counts rows.
Scenario analysis in Excel: base, upside and downside cases
Switch a finance model between base, upside and downside cases with one driver cell, check each case with hand calculations, and see how scenarios differ from sensitivity grids.
Circular references in Excel: trace the loop before you iterate
Reproduce an accidental self-including SUM, then build a revolver loop that funds its own interest. Compare timing, algebraic and iteration fixes with checked numbers.
Percentage change in Excel: growth, declines and zero bases
Calculate percentage change in Excel with one formula, catch the ratio-not-change mistake, and handle negative and zero starting values and percentage points on a checked variance table.