How to Look Up a Payment Date Within a Sales Date Range in WPS Office Spreadsheets
Question details
The user needs to return the corresponding payment date when a sales date falls between a start date and an end date stored in another Excel table.
- Product
- WPS Office Spreadsheets
- Device & OS
- not provided
- Scenario
- The user has a sales table and a separate payment schedule table with start dates, end dates, and payment dates, and needs to map each sales date to the correct payment date range.
- Observed behavior
- The desired result is a payment date returned automatically for each sales date when it falls within the relevant start and end date range.
Open the workbook in WPS Office Spreadsheets and confirm the sales data and payment schedule are ready to convert into Excel tables. If you use the LOOKUP method, make sure the payment schedule's start-date column is sorted in ascending order and contains no blank cells.
Use LOOKUP with Excel Tables and Sorted Start Dates
Best for a simple lookup when the payment schedule start dates are sorted ascending and each row marks the beginning of a payment period.
The vector form of LOOKUP returns the largest start date that is less than or equal to the sales date. When start dates are sorted in ascending order, this matches the current payment period without needing to reference the end date directly.
Select the payment schedule range in WPS Office Spreadsheets, click Insert > Table, and enable the header row.
Click any cell in the Start Date column, go to Data > Sort Ascending, and confirm the range expands to include the whole table.
Select the sales data range, click Insert > Table, and make sure the Sales date column has a clear header.
Click the first payment date result cell in the sales table, type =LOOKUP([@[Sales date]],Table2[Sales date (Start)],Table2[Payment date]), and press Enter.
Use INDEX and MATCH to Check Both Start and End Dates in an Unsorted Table
Use this method when the payment schedule cannot be sorted or you must explicitly verify that the sales date is on or before the end date.
Return Payment Dates by Sales Date Range in WPS Office Spreadsheets
WPS Office Spreadsheets supports Excel tables, structured references, and lookup formulas, so you can retrieve the correct payment date for each sales date and save the workbook with Microsoft Excel-compatible .xlsx format.
- 1. Open the workbook in WPS Office Spreadsheets: Launch WPS Office Spreadsheets and open the workbook that contains the sales and payment schedule data.
- 2. Convert the payment schedule to a table: Select the payment schedule range, click Insert > Table, and confirm the header row.
- 3. Enter the lookup formula: Click the first payment date result cell in the sales table and enter the LOOKUP formula using your table and column names.
- 4. Confirm the results: Press Enter and verify that the table column returns the correct payment date for each sales date.
- 5. Save in Excel format: Save the file as .xlsx to preserve compatibility with Microsoft Excel.
Frequently Asked Questions
Why does my LOOKUP formula return #N/A for some sales dates?
The most common causes are a sales date earlier than the first start date or a start-date column that is not sorted in ascending order. Click a cell in the Start Date column, go to Data > Sort Ascending, and remove blank start dates.
Do I need to include the end date column in the LOOKUP formula?
Not for the sorted start-date method. LOOKUP matches the largest start date that is less than or equal to the sales date. If you need to explicitly validate the end date or cannot sort the ranges, use the INDEX and MATCH method instead.
Can I use this lookup when the payment schedule is on a different worksheet?
Yes. Create the table on the other worksheet and refer to it by its structured table and column names, such as Table2[Sales date (Start)] and Table2[Payment date].
How do I convert a normal data range into an Excel table in WPS Office?
Select the data range, click Insert > Table, or press Ctrl+T. In the Create Table dialog, confirm the range and enable the header row option, then click OK.
