OFFICE 365 - Return last entered value based on multiple conditions

[vitoaiaco]

Greetings to the Forum!

I kindly need your help with a formula in the attached spreadsheet. I'm using Office 365.

In the "raw data" tab I have a data set with various types of information relating to daily oil top ups for a fleet of trucks. In the "Summary" - COLUMN C (CURRENTLY OIL IN USE) - I would like to return the LATEST entry that meets the following conditions:

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"

The returned value in C3 (Summary tab)  should be the LATEST entry in COLUMN G (OIL TYPE IN USE) that meets ALL the above conditions.

Your help with this would be hugely appreciated!

Vito

[#2 · Accepted solution]

@vitoaiaco 

=IFERROR(INDEX('RAW DATA'!$G$1:$G$100,LARGE(IF((SUMMARY!A3='RAW DATA'!$C$1:$C$100)*('RAW DATA'!$E$1:$E$100="SERVICE")*('RAW DATA'!$F$1:$F$100="ENGINE"),ROW('RAW DATA'!$A$1:$A$100)),1)),"")

You can try this formula if column A in sheet "RAW DATA" is always sorted in ascending order. Enter the formula with ctrl+shift+enter if you don't work with Office 365 or Excel 2021.

[#3]

@vitoaiaco 

=TAKE(FILTER('RAW DATA'!$G$2:$G$80,('RAW DATA'!$C$2:$C$80=$A3)*('RAW DATA'!$E$2:$E$80="SERVICE")*('RAW DATA'!$F$2:$F$80="ENGINE"),""),-1)

[#4]

@vitoaiaco 

In converted the values in column A of the RAW DATA sheet to dates using Data > Text to Columns.

In C2:

=LET(MaxDate,MAXIFS('RAW DATA'!$A$2:$A$1000,'RAW DATA'!$C$2:$C$1000,A3,'RAW DATA'!$E$2:$E$1000,"SERVICE",'RAW DATA'!$F$2:$F$1000,"ENGINE"),IF(MaxDate=0,"-",INDEX('RAW DATA'!$G$2:$G$1000,MATCH(1,('RAW DATA'!$A$2:$A$1000=MaxDate)*('RAW DATA'!$C$2:$C$1000=A3)*('RAW DATA'!$E$2:$E$1000="SERVICE")*('RAW DATA'!$F$2:$F$1000="ENGINE"),0))))

Fill down.

[#5]

Guys, thank you so so much for your responses!
Solution was found, much appreciated

[#6]

Thank you so so much, this formula perfectly fixed all my problems!

Take care!
Vito