Formula Errors

How to Combine Daily, Weekly, and Monthly Calendars in Excel

Guest Writer Oct 12, 2026 869 views

Question details

The user wants to automatically display recurring activities, assignments, or events from daily or weekly data lists into a consolidated monthly calendar view with category filtering.

How to Combine Daily, Weekly, and Monthly Calendars in Excel

Product: Excel
Device & OS: not provided
Scenario: Managing event schedules or assignments across different timeframes and needing a unified monthly calendar view populated by a source data table.
Observed behavior: The user needs a working formula to automatically pull and match event names into the correct dates and categories on the monthly tab without manually copying the data.

Before you start

Ensure your source data is organized in a clear tabular format with dedicated columns for Dates, Categories, Times, and Event Names. Verify that the date formatting in your source data matches the date formatting used in your monthly calendar view.

Solution 1Recommended

Use INDEX and MATCH Formulas to Populate the Monthly Calendar

Using a combination of INDEX, MATCH, and IFERROR allows you to pull specific events based on both date and category dynamically.

This approach concatenates multiple criteria (like date and category) to look up and return the exact event name from your source list to your monthly calendar cells. The IFERROR function ensures that days without events simply appear blank.

Set Up Source Data

Create a Source Data table containing columns for Date, Category, and Event Name. Note the exact range of this data (for example, A17:C26).

Select the Target Cell

In your Monthly Calendar tab, click on the first cell where you want an event to appear (e.g., a cell intersecting a specific date in row 4 and a category in column A).

Enter the Formula

Type the formula: =IFERROR(INDEX($A$17:$C$26, MATCH(B$4&$A5, $A$17:$A$26&$B$17:$B$26, 0), 3), ""). Press Enter. If you are using an older version of Excel, press Ctrl+Shift+Enter to evaluate it as an array formula.

Apply to the Entire Calendar

Click the bottom-right corner of the cell with the formula and drag the fill handle to copy it across the remaining days and categories in your calendar view.

Use INDEX and MATCH Formulas to Populate the Monthly Calendar

Adjust Cell References: Make sure to adjust the absolute references (like $A$17:$C$26) to match the actual location and size of your source data table in your specific workbook.

Solution 2

Customize an Existing Excel Calendar Template

If you prefer not to build a calendar and configure lookup arrays from scratch, modifying a pre-made template is a fast alternative.

Create and Combine Calendars Easily with WPS Office

WPS Spreadsheet offers powerful lookup functions, array formulas, and a rich library of free calendar templates to manage your daily, weekly, and monthly schedules efficiently.

1. Launch WPS Spreadsheet: Open WPS Spreadsheet and create a new workbook or search for a 'Calendar' template from the extensive online library.
2. Prepare Source Data: Set up your source data on a new tab with Date, Category, and Event Name columns.
3. Apply the Lookup Formula: Navigate to your monthly view and use the formula =IFERROR(INDEX(source_range, MATCH(date&category, date_range&category_range, 0), column_index), "") to automatically retrieve your events.
4. Save and Share: Save your newly automated calendar as an .xlsx file to easily share it or edit it across different platforms seamlessly.

Fully compatible with Microsoft Excel (.xlsx) formats and formulasRich library of free, customizable calendar and planner templatesAdvanced formula support including INDEX, MATCH, and IFERRORLightweight application that runs smoothly on all devices

microsoft office alternative - wps office

Frequently Asked Questions

Why is my INDEX MATCH formula returning an error or blank?

This usually happens if the date formats in your source data and monthly calendar do not perfectly match, or if the lookup ranges in the MATCH function are not locked with absolute references (using $ signs). Since IFERROR is used, errors will display as blank cells.

Can I combine events from multiple tabs into one monthly calendar?

Yes, you can use lookup functions across different sheets by prefixing the cell references with the sheet name. For example, your source range might look like 'Daily Data'!$A$1:$C$100 inside the INDEX function.

Do I need to press Ctrl+Shift+Enter for this formula?

In newer versions of Excel or WPS Spreadsheet that support dynamic arrays, pressing Enter is sufficient. If you are using an older version, concatenating arrays inside the MATCH function requires an array formula, so you must use Ctrl+Shift+Enter for it to calculate correctly.

How do I handle multiple events on the same day in this formula?

The standard INDEX MATCH setup returns only the first match it finds. To display multiple events on the same day, you would need to use a more complex array formula incorporating the SMALL and ROW functions, or use the newer FILTER function if supported by your software version.

Excel & Data Problems