How to Use Excel 365 LET and XLOOKUP to Subtract the Latest Matching Service Mileage in WPS Spreadsheet
Question details
The user needs a formula that finds the latest raw-data entry matching a fleet number, a SERVICE transaction type, and an ENGINE component, then subtracts that value from the latest mileage on the Summary sheet.
- Product
- WPS Spreadsheet
- Device & OS
- not provided
- Scenario
- Maintenance tracking in a workbook with raw data and a Summary sheet where the user wants an automatic calculation for the latest matching service mileage.
- Observed behavior
- The expected goal is a formula that uses a reverse lookup on concatenated criteria and returns the latest mileage minus the latest matching service mileage.
Confirm that the raw data columns for fleet number, transaction type, component, and mileage/km-hours are in consistent row alignment, and set up named ranges or table references before entering the formula.
Use LET and XLOOKUP with a Reverse Search
Best for Excel 365-compatible workbooks in WPS Spreadsheet when you want one self-contained formula that finds the latest SERVICE+ENGINE record and returns the difference from the latest mileage.
This formula concatenates FleetNo, "Service", and "Engine" into a single lookup value and creates a matching concatenated array. XLOOKUP then searches from bottom to top with -1, so the latest raw-data row wins.
Open the workbook in WPS Spreadsheet, go to the Summary sheet, and click the cell where you want the remaining mileage or interval result.
Type =LET(lookup,FleetNo&"Service"&"Engine",lookup_arr,Raw_Fleet&TransType&Component,last_raw,XLOOKUP(lookup,lookup_arr,KMHRS,0,0,-1),IFERROR(LatestMileage-last_raw,LatestMileage)) and press Enter.
Test with a fleet number that has multiple SERVICE and ENGINE records, and confirm the formula subtracts only the latest matching service mileage.
Use a FILTER-Based LET Formula to Exclude Non-Engine Services
Use this when you want the formula to explicitly filter out rows that are not SERVICE and ENGINE before performing the latest-value lookup.
Use WPS Spreadsheet to Run Excel 365 LET and XLOOKUP Mileage Formulas
WPS Spreadsheet supports Excel 365-style dynamic array formulas such as LET, XLOOKUP, and FILTER, so you can subtract the latest matching service mileage directly in your workbook without purchasing or switching to another Office suite.
- 1. Open your workbook in WPS Spreadsheet: Click Download WPS Office Free, install WPS Office, and open the Excel workbook that contains the raw data and Summary sheet.
- 2. Review named ranges: Go to Formulas > Name Manager in WPS Spreadsheet and verify the named ranges for FleetNo, Raw_Fleet, TransType, Component, KMHRS, and LatestMileage.
- 3. Enter the recommended formula: Click the Summary result cell, enter the LET/XLOOKUP formula from the solution above, and press Enter.
- 4. Confirm with new data: Add a newer SERVICE+ENGINE raw-data row and confirm that the formula result updates to use the newest matching mileage.
Frequently Asked Questions
Why does the formula use XLOOKUP instead of VLOOKUP?
XLOOKUP can search from bottom to top with the -1 search mode and does not require the lookup column to be the leftmost column, making it a cleaner way to find the latest matching raw-data row.
What happens if there is no SERVICE and ENGINE record for a fleet number?
The IFERROR fallback returns the LatestMileage value, so the subtraction result is 0 or blank depending on the wrapper. You can replace LatestMileage with a custom text such as "No engine service found" inside IFERROR.
Can this formula be adapted to ignore non-engine service rows before the lookup?
Yes. Use a FILTER-based LET formula that creates arrays only where the transaction type is "service" and the component is "engine", then run XLOOKUP against those filtered arrays.
Does WPS Spreadsheet support LET, XLOOKUP, and FILTER dynamic array formulas?
Recent WPS Spreadsheet versions support Excel 365-style dynamic array functions. Keep WPS Office updated so the formula engine has the latest function support.
