Author: Oscar Cronquist Article last updated on January 18, 2019

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?

Answer:

Sheet Bills

Sheet Reminder

Array formula in cell B1 sheet Reminder:

=IFERROR(INDEX(Table1[Bill], SMALL(IF((MONTH($C$2)=(MONTH(Table1[Due date])+(COLUMN($A$1:$L$1)-1)*Table1[Frequency (months)]))*((DAY($C$2)<$C$4)*(DAY(Table1[Due date])>=$D$4)+(DAY($C$2)>=$C$4)*(DAY($C$2)<$D$4)*(DAY(Table1[Due date])>=$C$4)*(DAY(Table1[Due date])<$D$4)+(DAY($C$2)>=$D$4)*(DAY(Table1[Due date])>=$D$4)), ROW(Table1[Due date])-MIN(ROW(Table1[Due date]))+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.

How to create an Excel defined Table

I am using an Excel defined Table because a cell reference to an Excel defined table adjusts automatically if you add or remove records.

  1. Select a cell in the data set sheet Reminder.
  2. Press CTRL + T.
  3. Click checkbox if your data set has headers.
  4. Click OK button.

Explaining array formula in cell B7

The remaining array formulas in cell C7 and D7 are similar to the one in cell B7, they extract a different part of the Excel defined Table.

Step 1

The MONTH function returns a number representing a month based on an Excel date. 1= Jan, 2 = Feb, 3 = March ... 12 = Dec.

MONTH($C$2)=(MONTH(Table1[Due date])+(COLUMN($A$1:$L$1)-1)*Table1[Frequency (months)])

becomes

4=(MONTH(Table1[Due date])+(COLUMN($A$1:$L$1)-1)*Table1[Frequency (months)])

becomes

4={1;1;1;1;1;1;1}+(COLUMN($A$1:$L$1)-1)*Table1[Frequency (months)])

becomes

4={1;1;1;1;1;1;1}+({1,2,3,4,5,6,7,8,9,10,11,12}-1)*Table1[Frequency (months)])

becomes

4={1;1;1;1;1;1;1}+({0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11})*Table1[Frequency (months)])

becomes

4={1;1;1;1;1;1;1}+({0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11})*{1;3;1;6;12;12;6})

becomes

4={1;1;1;1;1;1;1}+{0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11;0, 3, 6, 9, 12, 15, 18, 21, 24, 27, 30, 33;0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11;0, 6, 12, 18, 24, 30, 36, 42, 48, 54, 60, 66;0, 12, 24, 36, 48, 60, 72, 84, 96, 108, 120, 132;0, 12, 24, 36, 48, 60, 72, 84, 96, 108, 120, 132;0, 6, 12, 18, 24, 30, 36, 42, 48, 54, 60, 66}

becomes

4={1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12;1, 4, 7, 10, 13, 16, 19, 22, 25, 28, 31, 34;1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12;1, 7, 13, 19, 25, 31, 37, 43, 49, 55, 61, 67;1, 13, 25, 37, 49, 61, 73, 85, 97, 109, 121, 133;1, 13, 25, 37, 49, 61, 73, 85, 97, 109, 121, 133;1, 7, 13, 19, 25, 31, 37, 43, 49, 55, 61, 67}

and returns

{FALSE, FALSE, FALSE, TRUE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE;FALSE, TRUE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE;FALSE, FALSE, FALSE, TRUE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE;FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE;FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE;FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE;FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE}

Step 2

The DAY function returns the day of an Excel date.

(DAY($C$2)<$C$4)*(DAY(Table1[Due date])>=$D$4)

becomes

(19<5)*({15;7;25;14;24;10;16}>=19)

becomes

FALSE*{FALSE; FALSE; TRUE; FALSE; TRUE; FALSE; FALSE}

and returns {0; 0; 0; 0; 0; 0; 0}

Step 3

(DAY($C$2)>=$C$4)*(DAY($C$2)<$D$4)*(DAY(Table1[Due date])>=$C$4)*(DAY(Table1[Due date])<$D$4)

becomes

(19>=5)*(19<19)*({15;7;25;14;24;10;16}>=5)*({15;7;25;14;24;10;16}<19)

becomes

TRUE*FALSE*{1;1;0;1;0;1;1}

and returns

{0; 0; 0; 0; 0; 0; 0}

Step 4

(DAY($C$2)>=$D$4)*(DAY(Table1[Due date])>=$D$4)

becomes

(19>=19)*({15;7;25;14;24;10;16}>=19)

becomes

TRUE*{FALSE; FALSE; TRUE; FALSE; TRUE; FALSE; FALSE}

and returns

{0; 0; 1; 0; 1; 0; 0}

Step 5 - Add/Multiply arrays

AND logic - Multiply arrays

OR logic - Add arrays

(MONTH($C$2)=(MONTH(Table1[Due date])+(COLUMN($A$1:$L$1)-1)*Table1[Frequency (months)]))*((DAY($C$2)<$C$4)*(DAY(Table1[Due date])>=$D$4)+(DAY($C$2)>=$C$4)*(DAY($C$2)<$D$4)*(DAY(Table1[Due date])>=$C$4)*(DAY(Table1[Due date])<$D$4)+(DAY($C$2)>=$D$4)*(DAY(Table1[Due date])>=$D$4))

becomes

{FALSE, FALSE, FALSE, TRUE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE;FALSE, TRUE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE;FALSE, FALSE, FALSE, TRUE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE;FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE;FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE;FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE;FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE}*({0; 0; 0; 0; 0; 0; 0}+{0; 0; 0; 0; 0; 0; 0}+{0; 0; 1; 0; 1; 0; 0})

becomes

{FALSE, FALSE, FALSE, TRUE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE;FALSE, TRUE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE;FALSE, FALSE, FALSE, TRUE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE;FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE;FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE;FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE;FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE, FALSE}*{0;0;1;0;1;0;0}

and returns

{0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0; 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0; 0, 0, 0, 1, 0, 0, 0, 0, 0, 0, 0, 0; 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0; 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0; 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0; 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0}

Step 6 - Convert TRUE (1) to corresponding row number

The IF function has three arguments, the first one must be a logical expression. If the expression evaluates to TRUE then one thing happens (argument 2) and if FALSE another thing happens (argument 3).

IF((MONTH($C$2)=(MONTH(Table1[Due date])+(COLUMN($A$1:$L$1)-1)*Table1[Frequency (months)]))*((DAY($C$2)<$C$4)*(DAY(Table1[Due date])>=$D$4)+(DAY($C$2)>=$C$4)*(DAY($C$2)<$D$4)*(DAY(Table1[Due date])>=$C$4)*(DAY(Table1[Due date])<$D$4)+(DAY($C$2)>=$D$4)*(DAY(Table1[Due date])>=$D$4)), ROW(Table1[Due date])-MIN(ROW(Table1[Due date]))+1, "")

becomes

IF({0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0; 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0; 0, 0, 0, 1, 0, 0, 0, 0, 0, 0, 0, 0; 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0; 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0; 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0; 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0}, ROW(Table1[Due date])-MIN(ROW(Table1[Due date]))+1, "")

becomes

IF({0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0; 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0; 0, 0, 0, 1, 0, 0, 0, 0, 0, 0, 0, 0; 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0; 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0; 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0; 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0}, {1;2;3;4;5;6;7}, "")

and returns

{"", "", "", "", "", "", "", "", "", "", "", "";"", "", "", "", "", "", "", "", "", "", "", "";"", "", "", 3, "", "", "", "", "", "", "", "";"", "", "", "", "", "", "", "", "", "", "", "";"", "", "", "", "", "", "", "", "", "", "", "";"", "", "", "", "", "", "", "", "", "", "", "";"", "", "", "", "", "", "", "", "", "", "", ""}

Step 7 - Extract k-th smallest row number in array

The SMALL function returns the k-th smallest value in a cell range or array. SMALL( array, k)

SMALL(IF((MONTH($C$2)=(MONTH(Table1[Due date])+(COLUMN($A$1:$L$1)-1)*Table1[Frequency (months)]))*((DAY($C$2)<$C$4)*(DAY(Table1[Due date])>=$D$4)+(DAY($C$2)>=$C$4)*(DAY($C$2)<$D$4)*(DAY(Table1[Due date])>=$C$4)*(DAY(Table1[Due date])<$D$4)+(DAY($C$2)>=$D$4)*(DAY(Table1[Due date])>=$D$4)), ROW(Table1[Due date])-MIN(ROW(Table1[Due date]))+1, ""), ROW(A1))

becomes

SMALL({"", "", "", "", "", "", "", "", "", "", "", "";"", "", "", "", "", "", "", "", "", "", "", "";"", "", "", 3, "", "", "", "", "", "", "", "";"", "", "", "", "", "", "", "", "", "", "", "";"", "", "", "", "", "", "", "", "", "", "", "";"", "", "", "", "", "", "", "", "", "", "", "";"", "", "", "", "", "", "", "", "", "", "", ""}, ROW(A1))

becomes

SMALL({"", "", "", "", "", "", "", "", "", "", "", "";"", "", "", "", "", "", "", "", "", "", "", "";"", "", "", 3, "", "", "", "", "", "", "", "";"", "", "", "", "", "", "", "", "", "", "", "";"", "", "", "", "", "", "", "", "", "", "", "";"", "", "", "", "", "", "", "", "", "", "", "";"", "", "", "", "", "", "", "", "", "", "", ""}, 1)

and returns 3.

Step 8 - Return value

The INDEX function returns a value based on a row and column number.

INDEX(Table1[Bill], SMALL(IF((MONTH($C$2)=(MONTH(Table1[Due date])+(COLUMN($A$1:$L$1)-1)*Table1[Frequency (months)]))*((DAY($C$2)<$C$4)*(DAY(Table1[Due date])>=$D$4)+(DAY($C$2)>=$C$4)*(DAY($C$2)<$D$4)*(DAY(Table1[Due date])>=$C$4)*(DAY(Table1[Due date])<$D$4)+(DAY($C$2)>=$D$4)*(DAY(Table1[Due date])>=$D$4)), ROW(Table1[Due date])-MIN(ROW(Table1[Due date]))+1, ""), ROW(A1)), 1)

becomes

INDEX(Table1[Bill], 3, 1)

becomes

INDEX({"Rent";"Phone";"Electrical";"Garbage";"Cable Tv";"Newspaper";"Insurance"}, 3, 1)

and returns "Electrical" in cell B7.

Step 9 - Avoid errors

The IFERROR function returns a blank if formula returns an error.

Note, the IFERROR function catches all errors. This can make it much harder to spot other errors in the formula. Use with caution.

Array formula in cell C1:

=IFERROR(INDEX(DAY(Table1[Due date]), SMALL(IF((MONTH($C$2)=(MONTH(Table1[Due date])+(COLUMN($A$1:$L$1)-1)*Table1[Frequency (months)]))*((DAY($C$2)<$C$4)*(DAY(Table1[Due date])>=$D$4)+(DAY($C$2)>=$C$4)*(DAY($C$2)<$D$4)*(DAY(Table1[Due date])>=$C$4)*(DAY(Table1[Due date])<$D$4)+(DAY($C$2)>=$D$4)*(DAY(Table1[Due date])>=$D$4)), ROW(Table1[Due date])-MIN(ROW(Table1[Due date]))+1, ""), ROW(A1)), 1), "")

Array formula in cell D1:

=IFERROR(INDEX(Table1[Amount], SMALL(IF((MONTH($C$2)=(MONTH(Table1[Due date])+(COLUMN($A$1:$L$1)-1)*Table1[Frequency (months)]))*((DAY($C$2)<$C$4)*(DAY(Table1[Due date])>=$D$4)+(DAY($C$2)>=$C$4)*(DAY($C$2)<$D$4)*(DAY(Table1[Due date])>=$C$4)*(DAY(Table1[Due date])<$D$4)+(DAY($C$2)>=$D$4)*(DAY(Table1[Due date])>=$D$4)), ROW(Table1[Due date])-MIN(ROW(Table1[Due date]))+1, ""), ROW(A1)), 1), "")

Download Excel file

Enter your email to receive the workbook.
* You will also get a weekly newsletter, unsubscribe anytime!