Create a Sorted Unique List Between Two Dates in Excel
Question details
Return an alphabetically sorted list of unique table values for rows where a lookup date falls between the start and finish dates.

- 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.
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.
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.
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.
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.
Press Enter. FILTER keeps rows where both date comparisons are true, UNIQUE removes duplicates, and SORT arranges the resulting values alphabetically.
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.

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. Open the workbook: Open WPS Spreadsheet and use Menu > Open to select your Excel workbook.
- 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. 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. 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.

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.




