Return the Latest Value Based on Multiple Conditions in WPS Spreadsheets
Question details
The user needs a WPS Spreadsheets formula that checks the Raw Data sheet and returns the most recent value when fleet number, transaction type, and component match the specified criteria.
- Product
- WPS Spreadsheets
- Device & OS
- not provided
- Scenario
- The user keeps maintenance records on a Raw Data sheet and needs to pull the latest service or component value into a summary cell based on multiple matching criteria.
- Observed behavior
- The formula must identify the latest date before returning the related value; if no match exists, it should return "-" instead of an error.
Confirm that the date column contains real WPS/Excel date values rather than text, and note the exact worksheet ranges for Date, Fleet Number, Transaction Type, Component, and the value you want to return.
Use MAXIFS with INDEX and MATCH for a dynamic latest-value lookup
Use this method when your WPS Spreadsheets version supports MAXIFS; it finds the newest date that meets all criteria and returns the value from the matching row.
The formula first uses MAXIFS to calculate the latest date for the selected fleet number, SERVICE transaction type, and ENGINE component. The result is then fed into an INDEX/MATCH lookup that confirms the exact row by checking the date and all three criteria together.
Click the cell where the result should appear, such as C2 on the sheet that contains the fleet number in A2.
Type =IFERROR(INDEX('RAW DATA'!$G$2:$G$1000,MATCH(1,('RAW DATA'!$A$2:$A$1000=MAXIFS('RAW DATA'!$A$2:$A$1000,'RAW DATA'!$C$2:$C$1000,A2,'RAW DATA'!$E$2:$E$1000,"SERVICE",'RAW DATA'!$F$2:$F$1000,"ENGINE"))*('RAW DATA'!$C$2:$C$1000=A2)*('RAW DATA'!$E$2:$E$1000="SERVICE")*('RAW DATA'!$F$2:$F$1000="ENGINE"),0)),"-") and press Enter.
Drag the fill handle from the formula cell down to apply it to all fleet number rows.
Use a MAX(IF) array formula if MAXIFS is not available
Use this fallback when working in an older WPS Spreadsheets version or a workbook that must avoid MAXIFS.
Return the latest matching value with WPS Spreadsheets
Use WPS Spreadsheets to build a MAXIFS and INDEX/MATCH lookup that finds the latest date for your fleet number, transaction type, and component, then returns the related value from the Raw Data sheet.
- 1. Open your workbook in WPS Spreadsheets: Start WPS Office, click Open, and load the .xlsx workbook that contains the Raw Data sheet.
- 2. Enter the latest-value formula: In the result cell, enter the MAXIFS + INDEX + MATCH formula and adjust the criteria ranges and values to match your sheet.
- 3. Copy and save: Fill the formula down for all fleet numbers, then click Menu > Save As and keep the file in .xlsx format.
Frequently Asked Questions
Can WPS Spreadsheets use the same latest-value formula as Microsoft 365?
Yes. WPS Spreadsheets supports the MAXIFS, INDEX, and MATCH functions, so you can use the MAXIFS-based formula shown above. Make sure your date column contains real dates.
Why does the formula return "-" or an error?
A "-" result means IFERROR caught a no-match condition; check that the date, fleet number, transaction type, and component values exactly match the criteria. Errors often mean the date column is stored as text.
What should I do if my WPS version does not support MAXIFS?
Use the MAX(IF(...)) array formula fallback in this article. It produces the same latest-date match using classic functions.
Can I replace "SERVICE" and "ENGINE" with cell references?
Yes. Put the transaction type and component criteria in separate cells and replace the quoted criteria in the formula with those cell references, such as $E$1 and $F$1.
