Function Problems

Create a Sorted Unique List Between Two Dates in Excel

Muhammad TalhaMuhammad Talha Nov 18, 2026 869 views

Question details

Return an alphabetically sorted list of unique table values for rows where a lookup date falls between the start and finish dates.

Create a Sorted Unique List Between Two Dates in Excel
Product
Microsoft Excel
Device & OS
not provided
Scenario
A table contains values to list and start and finish dates for each row; a lookup date determines which rows qualify.
Observed behavior
The desired result is a spilled list containing each matching value once, sorted alphabetically.
Before you start

Check that your Excel version supports FILTER, UNIQUE, and SORT, and confirm that the lookup date and table dates are stored as real Excel dates. Identify the actual table and column names, and choose an empty output area outside the table.

Solution 1Recommended

Use FILTER, UNIQUE, and SORT to list matching values

Use this formula to return distinct values when the lookup date is on or between each row's start and finish dates.

1
Check the source fields

In Table2, identify the value column and the start and finish date columns. In this example, they are ColumnD, ColumnY, and ColumnZ, respectively, and E3 contains the lookup date.

2
Enter the formula

Select an empty cell outside the table and enter =SORT(UNIQUE(FILTER(Table2[ColumnD],(Table2[ColumnY]<=E3)*(Table2[ColumnZ]>=E3)))). Replace the table name, column names, and E3 with your own references.

3
Check the results

Press Enter. FILTER keeps rows where both date comparisons are true, UNIQUE removes duplicates, and SORT arranges the resulting values alphabetically.

4
Clear a blocked spill area

If the formula shows #SPILL!, click the formula cell to see the outlined output area, then clear any contents blocking it or move the formula to an empty area outside the table.

Use FILTER, UNIQUE, and SORT to list matching values
Date boundaries are included: The <= and >= comparisons include rows whose start or finish date equals the lookup date. If cells contain times as well as dates, those times can affect the comparison.
Work with dates in WPS Spreadsheet

Build a Sorted Date-Matched List in WPS Spreadsheet

In a WPS Spreadsheet version that supports FILTER, UNIQUE, and SORT, use the same dynamic-array approach to list distinct values for a lookup date.

  1. 1. Open the workbook: Open WPS Spreadsheet and use Menu > Open to select your Excel workbook.
  2. 2. Check the dates and ranges: Locate the value, start-date, and finish-date columns, and enter the lookup date in E3. Confirm that the date cells contain dates rather than text.
  3. 3. Enter the formula: Select an empty cell and enter =SORT(UNIQUE(FILTER(D2:D100,(Y2:Y100<=E3)*(Z2:Z100>=E3)))). Adjust the ranges and lookup cell to match your sheet.
  4. 4. Review the list: Press Enter and check the results. If the output is blocked, clear the cells below the formula or move it to an empty area.
Filter values against start and finish dates and sort the unique resultsOpen and edit Microsoft Excel XLSX and XLS filesUse a familiar spreadsheet interfaceWork with spreadsheets using the free version of WPS Office
microsoft office alternative - wps office

Frequently Asked Questions

Does the formula include the start and finish dates?

Yes. ColumnY<=E3 and ColumnZ>=E3 include rows where E3 equals either boundary. Change the operators to < and > if both boundaries should be excluded.

Why does Excel show #SPILL!?

The result needs multiple cells, but its output area may be blocked or the formula may be inside an Excel table. Click the formula cell to inspect the spill area, clear blocking cells, or move the formula outside the table.

What happens if no rows match the lookup date?

FILTER returns a #CALC! error if no rows match and no fallback is supplied. Add a third FILTER argument, such as "No matches", to display a message instead.

Why might a matching date be excluded?

Check that E3 and the start and finish columns contain real dates, not text. Also check for hidden time values, which can cause a date-and-time comparison to fall outside the range.