I want the result of the formula to be $28 because it matches the last date.

Thanks

=INDEX(E2:E6,MATCH(1,(H2=B2:B6)*(H3=C2:C6)*(H4=D2:D6),0))

CodePudding user response：

Perhaps you could try using a formula like as shown below using LOOKUP() Function, provided the dates are sorted in ascending order.

• Formula used in cell H5

=LOOKUP(2,1/((H2=B2:B3)*(H3=C2:C3)*(H4=D2:D3)*(A2:A3<>"")),E2:E3)

However, if your dates are not sorted then you can try using the below formula, to get latest date price.

• Formula used in cell K2

   =INDEX($E$2:$E$6,MATCH(1,INDEX((MAX(IF((I2=$C$2:$C$6)*(J2=$D$2:$D$6)*(H2=$B$2:$B$6),
$A$2:$A$6,""))=$A$2:$A$6)*(I2=$C$2:$C$6)*(J2=$D$2:$D$6)*(H2=$B$2:$B$6),),0))

Note: Depending on one's Excel version this may needs to be keyed with CTRL SHIFT ENTER instead of Enter when exiting edit mode.