Return a Course Expiration Date from Two Drop-Down Lists in WPS Spreadsheet
Question details
Create a formula that returns the expiration date matching a selected participant and selected course.
- Product
- WPS Spreadsheet
- Device & OS
- not provided
- Scenario
- A course-tracking worksheet contains participant and course drop-down lists, and the user wants the expiration date to update when either selection changes.
- Observed behavior
- The result cell should display the expiration date for the row where both the participant and course match the selected values.
Arrange the source records so each participant-and-course combination appears on its own row. Confirm that the Participant, Course, and Expiration Date columns use consistent names and contain no extra spaces in their values.
Use XLOOKUP to match the participant and course selections
Use this method when your WPS Spreadsheet version supports XLOOKUP and your source data is organized as a table with separate Participant, Course, and Expiration Date columns.
Combining the selected participant and course creates a two-condition lookup key. The formula returns the expiration date from the source row where both values match.
In the source sheet, place the records in columns named Participant, Course, Completion Date, and Expiration Date. Convert the range to a table if desired and name it SourceData.
Select the participant input cell, such as B2, then choose Data > Data Validation and set its list source to the participant names. Repeat for the course input cell, such as C2, using the course list.
Click the result cell where the expiration date should appear and enter: =XLOOKUP(B2&C2,SourceData[Participant]&SourceData[Course],SourceData[Expiration Date],"Not found"). Replace B2, C2, and the table column names with the references used in your workbook.
Choose a participant and a course from the drop-down lists. Change either selection and verify that the expiration date updates or that "Not found" appears when no matching record exists.
Use a helper column with VLOOKUP for older workbook compatibility
Use this method when XLOOKUP is unavailable or when you need a simple lookup structure compatible with older spreadsheet versions.
Look Up Course Expiration Dates with WPS Spreadsheet
WPS Spreadsheet lets you build participant and course drop-down lists, then use XLOOKUP or a helper-column VLOOKUP formula to return the corresponding expiration date automatically.
- 1. Open the tracking workbook: Open the course-tracking file in WPS Office and select the worksheet containing the participant, course, and expiration-date records.
- 2. Create the selection lists: Select each input cell and use Data > Data Validation to create the participant and course drop-down lists.
- 3. Add the expiration lookup: Click the result cell and enter an XLOOKUP formula that combines the participant and course selections as the lookup value.
- 4. Verify the returned date: Select different participant and course combinations and confirm that WPS Spreadsheet returns the corresponding expiration date.
Frequently Asked Questions
Why does the expiration-date formula return "Not found"?
The selected participant and course combination does not exactly match a row in the source data. Check spelling, hidden spaces, and whether that participant completed the selected course.
Can I use separate drop-down lists for participants and courses?
Yes. Create each list with Data > Data Validation, then reference the participant cell and course cell together in the lookup formula, such as B2&C2.
Do I need a helper column for a two-condition lookup?
No, XLOOKUP can combine the Participant and Course columns directly. A helper column is mainly useful when using VLOOKUP or maintaining compatibility with older spreadsheet versions.
How should I format the returned expiration date?
Select the result cell, open Home > Number Format, and choose Date. If the result is a text value rather than a date, correct the Expiration Date values in the source table first.
