How to Use XLOOKUP in WPS Spreadsheets to Retrieve Class Dates from Multiple Excel Tables
Question details
The user wants to create a current and eight-week class snapshot by retrieving schedule dates and related information from another worksheet using XLOOKUP in WPS Spreadsheets.
- Product
- WPS Office Spreadsheets
- Device & OS
- not provided
- Scenario
- The user has class schedule data in another worksheet and needs to look up matching class dates and related information into a snapshot layout.
- Observed behavior
- The original accepted answer was removed, so the exact formula is unavailable and a reliable formula must be rebuilt from the class identifier and source table columns.
Before entering the formula, confirm that the source schedule data is structured as an Excel Table with a unique class identifier column and matching header names. Also verify that the lookup class identifier exists in the source table so XLOOKUP can return the expected result.
Use XLOOKUP with Excel Table References to Return a Single Class Date
Use this when each class identifier appears once in the source schedule table and you need one matching date or value per row.
XLOOKUP searches a lookup array and returns the corresponding value from a return array. When the source range is formatted as an Excel Table, structured references such as Table1[Class ID] and Table1[Start Date] make the formula easier to read and automatically include new rows.
In WPS Spreadsheets, select the source schedule range, go to the Insert tab or press Ctrl+T, check My table has headers, and rename the table to Table1 under Table Tools.
Open the snapshot worksheet and select the cell where you want the first class date to appear.
Type =XLOOKUP(A2,Table1[Class ID],Table1[Start Date],"Not found") and press Enter, replacing A2 with the cell that contains the class identifier and the column names with your actual headers.
Drag the fill handle across the current and eight-week date columns, updating the return column reference as needed for each schedule field.
Use FILTER to Return All Matching Class Rows
Use this when multiple rows in the source table share the same class identifier and you need to display all matching schedule records.
Use XLOOKUP in WPS Spreadsheets to Retrieve Class Dates
WPS Office Spreadsheets supports XLOOKUP and Excel Table structured references, so you can build a current and eight-week class snapshot directly in WPS without changing your Excel workflow.
- 1. Prepare your data: Open your workbook in WPS Spreadsheets, select the source schedule range, and press Ctrl+T to create an Excel Table with a class identifier column.
- 2. Write the XLOOKUP formula: In the snapshot sheet, click the first date cell and enter =XLOOKUP(A2,Table1[Class ID],Table1[Start Date],"Not found").
- 3. Build the eight-week snapshot: Add columns for each week and use the same XLOOKUP formula with the appropriate return column from the source table.
- 4. Verify the results: Check the returned dates in the current and eight-week columns and adjust table names, column names, or cell references to match your workbook.
Frequently Asked Questions
Why is XLOOKUP not returning my class date in WPS Spreadsheets?
Check that the lookup value exists in the source class identifier column, the source data is formatted as an Excel Table, and the structured column names in the formula match the actual table headers exactly.
Can I retrieve multiple matching class rows instead of only the first result?
Yes. Use the FILTER function with a table reference, such as =FILTER(Table1,Table1[Class ID]=A2,"No records"), to return every matching row for the class identifier.
Does WPS Office support XLOOKUP in Excel workbooks?
Yes. WPS Spreadsheets supports XLOOKUP and can open, edit, and save Excel .xlsx workbooks while preserving the formulas and workbook structure.
How do I make my XLOOKUP formula update automatically when new schedule rows are added?
Convert the source data to an Excel Table by selecting the range and pressing Ctrl+T, then use structured references such as Table1[Class ID] and Table1[Start Date] instead of fixed cell ranges.
What should I do if the original XLOOKUP formula was removed?
Rebuild the formula by identifying the class identifier cell in the snapshot, the lookup column in the source table, and the return column for the date or related information, then enter =XLOOKUP(lookup_value, lookup_array, return_array, "Not found").
