Forum Discussion
Solved
OFFICE 365 - Return last entered value based on multiple conditions
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
=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.
- Guys, thank you so so much for your responses!
Solution was found, much appreciated 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.
=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)=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.
- Thank you so so much, this formula perfectly fixed all my problems!
Take care!
Vito
