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 relat...
=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.
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.