Use IFERROR and AGGREGATE in WPS Spreadsheets to Find the Latest Course Date
Question details
The user needs a WPS Spreadsheets formula that looks up a student ID, returns the latest course date, and displays the corresponding course name using IFERROR and AGGREGATE.
- Product
- WPS Office Spreadsheets
- Device & OS
- not provided
- Scenario
- The user has a course register with student IDs, course names, and course dates and wants to retrieve the latest course for a selected student.
- Observed behavior
- The desired formula should return the latest date and course name while hiding errors when no matching row exists.
Prepare a worksheet with student IDs in column A, course names in column B, and real date values in column C. Leave E2 for the lookup ID, F2 for the latest date, and G2 for the course name.
Find the Latest Course Date and Course Name with IFERROR and AGGREGATE
Use this complete lookup when you need both the latest course date and the matching course name for a selected student ID.
Assume student IDs are in A2:A100, course names are in B2:B100, and course dates are in C2:C100. The lookup ID is entered in E2.
Select cell F2, type =IFERROR(AGGREGATE(14,6,C2:C100/(A2:A100=E2),1),"No course found"), and press Enter.
Select cell G2, type =IFERROR(INDEX(B2:B100,AGGREGATE(15,6,(ROW(B2:B100)-ROW(B2)+1)/((A2:A100=E2)*(C2:C100=F2)),1)),"No course found"), and press Enter.
Type a student ID in E2. F2 shows the latest course date, and G2 shows the corresponding course name.
Ignore Hidden Rows When Using AGGREGATE for the Latest Course Date
Use this version when the course list is filtered or hidden rows should not affect the latest-date result.
Use IFERROR and AGGREGATE in WPS Spreadsheets to Retrieve the Latest Course Date and Course Name
Do the lookup directly in WPS Spreadsheets with IFERROR and AGGREGATE. The formulas handle unmatched rows cleanly, return the latest course date and course name, and work in a free, Excel-compatible spreadsheet environment.
- 1. Open WPS Spreadsheets: Launch WPS Office, click Spreadsheets, and open the workbook that contains student IDs, course names, and course dates.
- 2. Enter the latest-date formula: Select cell F2, type =IFERROR(AGGREGATE(14,6,C2:C100/(A2:A100=E2),1),"No course found"), and press Enter.
- 3. Enter the course-name formula: Select cell G2, type =IFERROR(INDEX(B2:B100,AGGREGATE(15,6,(ROW(B2:B100)-ROW(B2)+1)/((A2:A100=E2)*(C2:C100=F2)),1)),"No course found"), and press Enter.
- 4. Test the lookup: Type a student ID in E2 and check that F2 shows the latest date and G2 shows the matching course name.
Frequently Asked Questions
Why does my AGGREGATE formula return #VALUE! instead of the latest date?
This usually means the Course Date column contains text instead of real date values, or the compared ranges have different sizes. Select the date column, confirm it is formatted as Date, and use equal-sized ranges such as A2:A100, C2:C100.
Can this formula update when I filter or hide rows in WPS Spreadsheets?
Yes. Change the AGGREGATE option from 6 to 7 in the latest-date formula. Option 7 ignores both hidden rows and error values, so the result updates for visible matching rows.
What if two courses have the same latest date for a student?
The course-name formula returns the first matching row. If you need a tie-breaker, add a helper column that gives a unique priority, such as enrollment time or course ID, and include it in the condition.
Do I need to use Ctrl+Shift+Enter for these AGGREGATE and IFERROR formulas?
In WPS Spreadsheets these formulas can normally be entered with Enter. If the result is not calculated correctly, update WPS Office and confirm that your version supports AGGREGATE.
