Function Problems

Excel: Display All Jobs Due on the Same Date in One Calendar Cell

Rana GarciaRana Garcia Nov 12, 2026 869 views

Question details

The user needs to display every job due on a selected date instead of returning only the first matching job.

Product
Microsoft Excel
Device & OS
not provided
Scenario
A calendar cell should list all matching jobs from the BP Jobs sheet when their due date equals the date in Calendar!F20.
Observed behavior
A standard INDEX and MATCH formula returns only the first matching job for the selected date.
Before you start

Confirm that the due dates in BP Jobs column Q are real Excel dates and that the job names are in column E. Use Microsoft 365 or Excel 2021, which support the FILTER dynamic array function.

Solution 1Recommended

Use FILTER and TEXTJOIN to list every matching job

Use this solution in Microsoft 365 or Excel 2021 when multiple jobs must appear in one calendar cell on separate lines.

FILTER returns every job whose due date matches Calendar!F20, while TEXTJOIN combines those results into one cell with line breaks.

1
Enter the dynamic array formula

Select the calendar cell, such as Calendar!F20 or the cell where the job list should appear, and enter =TEXTJOIN(CHAR(10),TRUE,FILTER('BP Jobs'!$E:$E,'BP Jobs'!$Q:$Q=Calendar!F20,""))

2
Confirm the formula

Press Enter. Excel evaluates FILTER and returns all job names from BP Jobs column E whose due date in column Q matches Calendar!F20.

3
Enable line wrapping

Select the result cell, open the Home tab, choose Wrap Text in the Alignment group, and adjust the row height so each matching job appears on its own line.

Multiple jobs now appear together: If no job matches the date, the formula returns a blank cell instead of an error.

Display All Matching Jobs in a WPS Spreadsheet Calendar Cell

WPS Spreadsheet can use FILTER and TEXTJOIN to collect every job with a matching due date and display the results as separate lines in one calendar cell. Enable Wrap Text to keep the list readable.

  1. 1. Open the workbook in WPS Spreadsheet: Launch WPS Office, choose Spreadsheet, and open the workbook containing the BP Jobs and Calendar sheets.
  2. 2. Enter the formula: Select the calendar output cell and enter =TEXTJOIN(CHAR(10),TRUE,FILTER('BP Jobs'!$E:$E,'BP Jobs'!$Q:$Q=Calendar!F20,"")), then press Enter.
  3. 3. Turn on Wrap Text: Select the formula cell, open the Home tab, and click Wrap Text in the Alignment group.
  4. 4. Adjust the row height: Drag the row boundary or use Home > Format > AutoFit Row Height so every matching job is visible on a separate line.
Lists every job matching a calendar date instead of returning only the first resultSupports dynamic array formulas such as FILTER and TEXTJOIN in compatible WPS Spreadsheet versionsOpens and saves Microsoft Excel .xlsx files with familiar spreadsheet editing toolsProvides a lightweight alternative for building calendars and tracking job due dates
microsoft office alternative - wps office

Frequently Asked Questions

Why does INDEX and MATCH return only one job?

INDEX and MATCH normally return a single value for the first matching position. Use FILTER to return all matching jobs, and combine it with TEXTJOIN when the results must appear in one cell.

Which Excel versions support this formula?

FILTER is available in Microsoft 365 and Excel 2021 or later. Older Excel versions may require helper columns, array formulas, or a different method to collect multiple matches.

How can I show each job on a new line?

Use TEXTJOIN with CHAR(10), then select the result cell and choose Home > Wrap Text. Increase the row height if the lines are not all visible.

What should I check if the formula returns no jobs?

Verify that Calendar!F20 and BP Jobs column Q contain matching date values, not text that only looks like dates. Also check that the sheet name and column references are correct.