# Lookup or Index Match or IF AND?

#### CTMom

##### New Member
I need to create a schedule of recurring expense payments on a weekly basis. Each expense is payable on a certain day of the month (eg. rent for property 1 due on the 1st, rent for property 2 due on the 10th). I want to then automatically populate a weekly cashflow schedule so that the amount of the rent is inserted in correct week. Eg. Property 1 rent goes in week where the 1st of the month falls.

I would appreciate any advice on best way to do this.
Thanks

Excel Workbook
ABCDEFGHI
8CategoryPaid on Day of MthAmount******
9Rent 111500******
10Rent 2152500******
11Rent 312000******
12Rent 4103000******
13*********
14*1/01/20118/01/201115/01/201122/01/201129/01/20115/02/201112/02/201119/02/2011
15Day Number* * * * * * * * * * * * * * * * * * * * * *1* * * * * * * * 8* * * * * * * * 15* * * * * * * * 22* * * * * * * * 29* * * * * * * * 5* * * * * * * * 12* * * * * * * * 19
16Rent 1********
17Rent 2********
18Rent 3********
19Rent 4********
Sheet2

### Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.
You have a few options I guess... one possibility:

Excel Workbook
ABCDEFGHIJ
8CategoryPaid on Day of MthAmount
9Rent 111500
10Rent 2152500
11Rent 3302000
12Rent 4103000
13
1401/01/201108/01/201115/01/201122/01/201129/01/201105/02/201112/02/201119/02/201126/02/2011
15Day Number181522295121926
16Rent 1150000015000001500
17Rent 2002500000250000
18Rent 3000020000002000
19Rent 4030000003000000
Sheet1

but I'm sure with more thought / brain power the above can be simplified

THANK YOU!!!

I knew I loved this Board. It is amazing.

I would never have figured this out and it works perfectly.

Thank you from the other side of the world.

I presumed the week headers were week commencing rather than week ending dates - this was not specified

Given the fact you say the results above are incorrect I presume you are saying that the dates are week ending dates, is that correct ?

When posting Jeanie screenshots you don't need to post all the formulas - set the Analyse Range to include a select few (as deemed necessary)
there's little value in repeating all of the formulas if they are consistent

I just looked at this again and given the dates specified are Sat it would seem you are using week ending rather than commencing - in truth I should have checked earlier !

Code:
``````B23:
=IF(MEDIAN(B\$15-6,B\$15,\$B9)=\$B9,\$C9,IF(AND(B\$15<6,OR(B\$15>=\$B9,DAY(B\$14-7)<\$B9)),\$C9,0))``````

Hopefully the above revision will suffice - others may have more elegant alternatives.

Thanks - works great.

Am novice with Excel Jeanie so just put it all in - sorry.

Can I ask how formula would change if I did this on daily schedule based on weekdays only?

Not sure I follow...are you saying horizontal axis would be Mon-Fri dates rather than week ending dates ?

If so, were the payment day to fall on a weekend or exceed the no. of days in month (eg 31 in April) in which day should the payment be placed ?

Best to post an example with some manually calculated expected results.

Yes you are correct - M-F on horizontal axis. If payment falls on a weekend then pay should be on Friday prior.

Excel Workbook
ABCDEFGHI
8CategoryPaid on Day of MthAmount
9Rent 161500
10Rent 2152500
11Rent 382000
12Rent 4123000
13
14Weekday4/03/20117/03/20118/03/20119/03/201110/03/201111/03/201114/03/201115/03/2011
15Day Number478910111415
16Rent 11500
17Rent 22500
18Rent 32000
19Rent 43000
Sheet1

Replies
2
Views
741
Replies
1
Views
564
Replies
2
Views
501
Replies
4
Views
398
Replies
1
Views
393

1,221,202
Messages
6,158,504
Members
451,497
Latest member
something68

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back