Return the Value from the Range that corresponds to Max Date.

[T.I.]

Good day,
I've stuck with the following task:
I want the formula that returns the value corresponding to newest date, or returns last entered value if dates are the same.
Also formula should consider two additional conditions.

My attempt:

=INDEX($E$2:$E$9,MATCH(MAX(IF(($A$2:$A$9)*($E$2:$E$9<>"")*($B$2:$B$9=G2)*($C$2:$C$9=H2)*($D$2:$D$9=I2),$A$2:$A$9)),$A$2:$A$9,0))  

[Pepe Le Mokko]

Try

Please Login or Register  to view this content.

and pull down
How can the last entered value be differentiated form the others?

[T.I.]

Pepe Le Mokko
Tried your formula, but seems the result is the same as mine.

can the last entered value be differentiated form the others

In J3 for "Procedure N2" value should be 24.

[Bo_Ry]

Maybe try

=LOOKUP(9^9,$E$2:$E$9/($B$2:$B$9=G2)/($D$2:$D$9=I2))
order 66 for the department6 column C, order 67 will not match.

or
=LOOKUP(9^9,$E$2:$E$9/($B$2:$B$9=G2)/($D$2:$D$9=I2)/($C$2:$C$9=H2))

[T.I.]

Pepe Le Mokko
Sorry, my mistake. Order should not be 67 - only 66. Mistakenly copied down with number change. Your formula works, but, doesn't consider the max date. For instance if I change the date to newest for the procedure N1 the value remains the same. But it should correspond to the newest date.

Bo_Ry
Good day, thanks for formula but result is the same as above. Your formula doesn't consider newest date.

[Bo_Ry]

It doesn't need to consider the date if the latest date entered on the bottom.

[T.I.]

Now I see, thank you. One remark. Formula returns zero if do not enter the latest date in the bottom keeping the relevant value cell empty. It should return previous available value for earlier date.

[Bo_Ry]

Add this
=LOOKUP(9^9,$E$2:$E$9/($B$2:$B$9=G2)/($D$2:$D$9=I2)/($C$2:$C$9=H2)/($E$2:$E$9>0))

[T.I.]

Bo_Ry
Excellence! Thank you so much!