Create a Weekly Excel 2016 Calendar with Multiple Events per Day
Question details
Create a weekly calendar in Excel 2016 that lists multiple events for each day without FILTER or TEXTJOIN.

- 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.
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.
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.
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.
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))),"").
Press Ctrl+Shift+Enter instead of Enter to confirm the formula as an array formula in Excel 2016.
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.

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. 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. 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. 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. 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.

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.




