OFFICE 365 - Subtraction between last entered value meeting criteria and another cell

[vitoaiaco]

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 B3 ("Summary" tab).

In order for the subtraction to take place, the following criteria needs to be met:

1. Matching Fleet number in the raw data and summary
2. Matching Transaction Type (Column E in raw data) as "SERVICE"
3. Matching Component (Column F in raw data) as "ENGINE"

Your help is appreciated as always!

Many Thanks

Vito 

[#2 · Accepted solution]

@vitoaiaco 

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)
)

[#3]

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 you

[#4]

@vitoaiaco 

The 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, "")
)

[#5]

@vitoaiaco 

Similar approach but, rather than concatenating search criteria, I filtered out rows that do not correspond to an engine service.  There are still issues such as the last recorded mileage being lower than the latest service mileage.

= LET(
    engineService?,  (dataTransType="service")*(dataComponent="engine"),
    filtered,        FILTER(dataFleetNum, engineService?),
    interval,        FILTER(distance, engineService?),
    priorVal,        XLOOKUP(fleetNum, filtered, interval, 0, 0,-1),
    HSTACK(priorVal, IFERROR(latestMileage - priorVal, ""))
  )

[#6]

@vitoaiaco The workbook

[#7]

Thank you Gentlemen and sorry for the late reply!
Both your solutions worked, I'm extremely thankful

Vito