Function Problems

Create a Weekly Excel 2016 Calendar with Multiple Events per Day

John WilsonJohn Wilson Nov 18, 2026 870 views

Question details

Create a weekly calendar in Excel 2016 that lists multiple events for each day without FILTER or TEXTJOIN.

Create a Weekly Excel 2016 Calendar with Multiple Events per Day
Product
Microsoft Excel 2016
Device & OS
not provided
Scenario
A user has event dates and descriptions on an Items sheet and wants to display matching events under each date in a weekly calendar.
Observed behavior
Each calendar date should show its matching events in separate cells, with unused cells left blank.
Before you start

Put event dates in Items!A1:A8 and descriptions in Items!B1:B8. Make sure the event dates and calendar headings are real Excel dates without time values, and leave enough rows below each heading for the events.

Solution 1Recommended

List daily events with an INDEX and SMALL array formula

Use this method in Excel 2016 to return successive matching events in separate calendar cells.

The formula finds matching dates and returns the corresponding descriptions in their order on the Items sheet. Each row below a date returns the next match.

1
Set up the week

On the calendar sheet, enter the first date in D2. In E2, enter =D2+1 and fill it across through J2 to create seven date headings.

2
Enter the event formula

Select D3 and enter =IFERROR(INDEX(Items!$B$1:$B$8,SMALL(IF(Items!$A$1:$A$8=D$2,ROW(Items!$B$1:$B$8)),ROW($A1))),"").

3
Confirm the array formula

Press Ctrl+Shift+Enter instead of Enter to confirm the formula as an array formula in Excel 2016.

4
Fill the calendar

Use the fill handle to copy D3 down for the maximum number of events you want to show per day, then fill the resulting cells across through column J.

List daily events with an INDEX and SMALL array formula
Adjusting the source: The example uses ranges that start in row 1. If your data starts on another row, adjust the ranges and replace ROW(Items!$B$1:$B$8) with a relative row calculation such as ROW(Items!$B$2:$B$9)-ROW(Items!$B$2)+1.
WPS Spreadsheets

Build the same weekly event calendar in WPS Spreadsheets

Use an INDEX and SMALL array formula in WPS Spreadsheets to place multiple matching events under each day of the week.

  1. 1. Prepare the event list: Open your workbook in WPS Spreadsheets. On a sheet named Items, enter event dates in A1:A8 and descriptions in B1:B8.
  2. 2. Add the week headings: On the calendar sheet, enter the first date in D2. Enter =D2+1 in E2 and drag the fill handle across through J2.
  3. 3. Enter the array formula: Select D3, enter =IFERROR(INDEX(Items!$B$1:$B$8,SMALL(IF(Items!$A$1:$A$8=D$2,ROW(Items!$B$1:$B$8)),ROW($A1))),""), and confirm it with Ctrl+Shift+Enter.
  4. 4. Fill and save: Drag the fill handle down for additional events and across through column J. Use File > Save As to save the calendar as an XLSX workbook.
List multiple events per date in separate cells.Open and save Microsoft Excel XLSX workbooks.Use familiar spreadsheet formulas and fill controls.Work with a free, easy-to-use office suite.
microsoft office alternative - wps office

Frequently Asked Questions

Why does the formula show a blank when an event exists?

Check that the date in Items!A1:A8 matches the calendar heading as a real date value. A hidden time value or a date stored as text can prevent an exact match.

How do I show more events for one day?

Select the formula cells below that day's heading and drag the fill handle farther down. Each additional row can display the next matching event.

What if my event list starts below the first row?

Update both source ranges and use relative positions for the INDEX formula. For example, for rows 2 through 9, use ROW(Items!$B$2:$B$9)-ROW(Items!$B$2)+1 inside SMALL.

Can the formula show events with separate start and end dates?

Not as written. It matches one event date to one calendar date; events spanning several days need additional start-date and end-date criteria in the IF calculation.