How to Sort Parts into Time-Based Columns in WPS Spreadsheets Using XLOOKUP
Question details
The user needs to generate a parts schedule by matching each part delivery interval against a predefined list of time intervals, using lookup formulas instead of manually copying values and allowing blank cells for unscheduled times.
- Product
- WPS Spreadsheets
- Device & OS
- not provided
- Scenario
- The user maintains a parts schedule and wants each part to appear under the correct time interval column based on its delivery time, without manual copy and paste.
- Observed behavior
- Manual copying and pasting is time-consuming; the desired result is a formula-driven schedule that returns the matching part when scheduled and a blank cell when no part matches the time interval.
Prepare your worksheet with a sorted time interval column, for example A2:A100, a column of part delivery times, such as B2:B100, and an adjacent column of part values, such as C2:C100. Make sure each part time and its value are aligned in the same row and that the time column is sorted numerically.
Create a time-interval column and fill part columns with XLOOKUP
Use this when each part time maps to one part value. It is the fastest way to populate the schedule and automatically leaves blank cells for unscheduled intervals.
XLOOKUP matches the time interval in column A against the source part times in column B and returns the corresponding part value from column C. If no match exists, the empty string argument returns a blank cell instead of an error.
In WPS Spreadsheets, enter the required time intervals in column A, for example A2:A100, then sort them in ascending order if they are not already sorted.
Click the first cell in the part column next to the first time interval where you want the schedule result to appear.
Type =XLOOKUP($A2,$B$2:$B$100,$C$2:$C$100,"") and press Enter. The $A2 reference locks the time-interval column so the formula works when filled across rows.
Drag the fill handle down the column or double-click it to apply the formula to all time intervals. Blank cells appear automatically when no part is scheduled at that time.
Return multiple parts for repeated times with FILTER
Use this when several parts share the same delivery time and you need all matching values for a time interval, not just the first match.
Sort Parts into Time-Based Columns with WPS Spreadsheets
WPS Spreadsheets supports XLOOKUP and FILTER, so you can turn a flat parts list into a clean time-based schedule. Create a sorted time interval column, reference aligned part time and value ranges, and let formulas fill the matching parts while empty slots remain blank.
- 1. Open WPS Spreadsheets and load your workbook: Launch WPS Office, click WPS Spreadsheets from the home screen, then open an existing .xlsx parts list or create a new blank workbook.
- 2. Prepare the time and part source columns: Enter sorted time intervals in column A, part delivery times in column B, and part values in column C. Keep each part's time and value in the same row.
- 3. Apply the XLOOKUP formula: Click the first result cell, enter =XLOOKUP($A2,$B$2:$B$100,$C$2:$C$100,""), and press Enter to return the first matching part.
- 4. Fill the formula down and format the schedule: Drag the fill handle down the part column, then apply cell borders or shading to distinguish filled time slots from blank unscheduled slots.
- 5. Save or export in Excel-compatible format: Click Menu, choose Save As, and select Excel Workbook (.xlsx) to keep the file compatible with Microsoft Excel users.
Frequently Asked Questions
Does WPS Spreadsheets support XLOOKUP for matching parts to time intervals?
Yes. WPS Spreadsheets supports XLOOKUP. Use a formula such as =XLOOKUP($A2,$B$2:$B$100,$C$2:$C$100,"") and fill it down the time interval column.
Why does my lookup show 0 or an error when no part is scheduled?
Make sure the last argument in XLOOKUP is set to an empty string: "". If the source cell is blank and formatted as a number, clear the formatting or wrap the formula in IF.
Can I return more than one part when multiple parts share the same delivery time?
Yes. Use FILTER to return all matches, for example =FILTER($C$2:$C$100,$B$2:$B$100=$A2,""), or combine the matches with TEXTJOIN if you want them in one cell.
Do the source part time and value columns need to stay aligned?
Yes. Keep the part times in one column and the corresponding part values in the adjacent column with the same row; otherwise the lookup returns the wrong value.
Can I save a WPS Spreadsheets parts schedule as a Microsoft Excel file?
Yes. Click Menu > Save As and choose Excel Workbook (.xlsx). The formulas and formatting remain compatible when the file is opened in Excel.
