Dear Sir,
I Have cash book in my office where closing balance rows always get changed.
For example, today I have closing balance in my column no: E7.
Whereas tomorrow it will be a E15.
My question is other side I have currency calculation and closing balance is constant that is J10.
Every time I need to change the formula manually to get closing balance from column E.
Can you please help me with formula where I will get closing balance automatically in column J7.
Hi Sarfraz,
If you have Microsoft 365 or Excel 2021, you can use XLOOKUP to find the last value in column E:
=XLOOKUP(1E+100,E5:E20,E5:E20,,-1,2)
or for earlier versions of Excel, you can use INDEX & MATCH:
=INDEX(E5:E20,MATCH(1E+100, E5:E20,1))
Note: change the cell references E5:E20 in the formulas above to meet your needs and allow for growth in the data.
Mynda
