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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Saqib Husain, SyedEngineerCommented:
Enter this formula in B33 and copy down and across

=IF(ISERROR(MATCH($A33,B$7:B$26,0)),"",B$6)
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Spreadsheets

From novice to tech pro — start learning today.