Forum Discussion
Solved
OFFICE 365 - Subtraction between last entered value meeting criteria and another cell
Greetings to the forum! Please find attached file for your reference. In cell E3 in the "Summary" tab I would like to find the LATEST entry in column D ("Raw Data" tab) from the value in cell B...
Try this one:
=LET( lookup, FleetNo & "Service" & "Engine", lookup_arr, Raw_Fleet & TransType & Component, last_raw, XLOOKUP(lookup, lookup_arr, KMHRS, 0, , -1), IFERROR(LatestMileage - last_raw, LatestMileage) )
Try this one:
=LET(
lookup, FleetNo & "Service" & "Engine",
lookup_arr, Raw_Fleet & TransType & Component,
last_raw, XLOOKUP(lookup, lookup_arr, KMHRS, 0, , -1),
IFERROR(LatestMileage - last_raw, LatestMileage)
)- Thank you very much!
I'm trying change the IFERROR function to return as an empty cell rather than performing the subtraction, but it's not working. Would you be able to help with this last request?
Thank youThe formula changes a bit. The XLOOKUP returns "" in the event of error then we check for number and do the subtraction.
=LET( lookup, FleetNo & "Service" & "Engine", lookup_arr, Raw_Fleet & TransType & Component, last_raw, XLOOKUP(lookup, lookup_arr, KMHRS, "", , -1), IF(ISNUMBER(last_raw), LatestMileage - last_raw, "") )
