Author: Oscar Cronquist Article last updated on April 19, 2011

Brad asks: I'm trying to use your formulas to create my own bill reminder sheet. I envision a workbook where you enter your bills due date and their frequency. However what makes mine different than yours is I'd love for it to auto populate the bills based on my pay periods. I'd want it to list the bills I have to pay with the corresponding check. In other words I dont care what date my phone bill is due, because I live hand to mouth, so their due date is actually the day I get paid, make sense? If I get paid on the 5th and then again on the 19th, the sheet should list all the bills I have between the 5th and 19th as bills I need to pay with my check on the 5th. if I have a bill due on the 18th it should still list it as to be paid from the 5th check because paying with my check on the 19th would be too late. make sense?


Sheet Bills

Sheet Reminder

Array formula in cell B1:

=IFERROR(INDEX(Bills!$B$3:$B$15, SMALL(IF((MONTH($C$2)=(MONTH(Bills!$C$3:$C$15)+(COLUMN($A$1:$L$1)-1)*Bills!$E$3:$E$15))*((DAY($C$2)<$C$4)*(DAY(Bills!$C$3:$C$15)>=$D$4)+(DAY($C$2)>=$C$4)*(DAY($C$2)<$D$4)*(DAY(Bills!$C$3:$C$15)>=$C$4)*(DAY(Bills!$C$3:$C$15)<$D$4)+(DAY($C$2)>=$D$4)*(DAY(Bills!$C$3:$C$15)>=$D$4)), ROW(Bills!$E$3:$E$15)-MIN(ROW(Bills!$E$3:$E$15))+1, ""), ROW(A1)), 1), "")

How to create an array formula

  1. Copy (Ctrl + c) and paste (Ctrl + v) array formula into formula bar.
  2. Press and hold Ctrl + Shift.
  3. Press Enter once.
  4. Release all keys.

Array formula in cell C1:

=IFERROR(DAY(INDEX(Bills!$C$3:$C$15, SMALL(IF((MONTH($C$2)=(MONTH(Bills!$C$3:$C$15)+(COLUMN($A$1:$L$1)-1)*Bills!$E$3:$E$15))*((DAY($C$2)<$C$4)*(DAY(Bills!$C$3:$C$15)>=$D$4)+(DAY($C$2)>=$C$4)*(DAY($C$2)<$D$4)*(DAY(Bills!$C$3:$C$15)>=$C$4)*(DAY(Bills!$C$3:$C$15)<$D$4)+(DAY($C$2)>=$D$4)*(DAY(Bills!$C$3:$C$15)>=$D$4)), ROW(Bills!$E$3:$E$15)-MIN(ROW(Bills!$E$3:$E$15))+1, ""), ROW(B1)), 1)), "") + CTRL + SHIFT + ENTER

Array formula in cell D1:

=IFERROR(INDEX(Bills!$D$3:$D$15, SMALL(IF((MONTH($C$2)=(MONTH(Bills!$C$3:$C$15)+(COLUMN($A$1:$L$1)-1)*Bills!$E$3:$E$15))*((DAY($C$2)<$C$4)*(DAY(Bills!$C$3:$C$15)>=$D$4)+(DAY($C$2)>=$C$4)*(DAY($C$2)<$D$4)*(DAY(Bills!$C$3:$C$15)>=$C$4)*(DAY(Bills!$C$3:$C$15)<$D$4)+(DAY($C$2)>=$D$4)*(DAY(Bills!$C$3:$C$15)>=$D$4)), ROW(Bills!$E$3:$E$15)-MIN(ROW(Bills!$E$3:$E$15))+1, ""), ROW(C1)), 1), "") + CTRL + SHIFT + ENTER

Explaining array formula in cell B1

To be continued...

Download excel sample file for this tutorial.

Bill reminder
(Excel 2007 Workbook *.xlsx)