In Excel I have data on leases payment amounts and payments dates. I need to show the amount of a payment in the correct row of payment date and in the column of the appropriate lease.

I have an Excel sheet showing lease details for 20 leases.
The main details are the payment amount each quarter and the dates of those payments.

Below each lease I have rows for their payment date.
I want the amount of each lease's quarterly amount (in row 6) to be shown in the relevant row (from 1/1/2017 below row 32) and in the column of the respective lease.

Excel-Showing-the-cost-alongside-th.xlsx
The file is attached.
How do I achieve this?

Thanks
Michael
Michael O'FlahertyAsked:
Who is Participating?
 
Saqib Husain, SyedConnect With a Mentor EngineerCommented:
Enter this formula in B33 and copy down and across

=IF(ISERROR(MATCH($A33,B$7:B$26,0)),"",B$6)
0
 
Rob HensonFinance AnalystCommented:
Try this in B33 and copied down and across.

=IF(IFERROR(IF(MONTH($A33)=MONTH($A32),"",MOD(DATEDIF(B$2,$A33,"m"),3)),"")=0,B$6,0)

@Syed, I had the same idea to start with but then realised that will only match the first couple of months that are already populated in the table. I think the OP wants to see the payments against future dates as per the list starting in row 32.
0
 
Michael O'FlahertyAuthor Commented:
Thank you Saqib Husain, Syed
Your solution was spot on.

Rob,
your idea would be good for ongoing dates because it populates down as far as the columns go, but as the leases have finish dates with no payments after the last date the populated there needs to be no amounts shown for each lease after the term is over.

Thanks you both for your help.
I'm grateful.

Michael
0
 
Michael O'FlahertyAuthor Commented:
Thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.