Try this (change the ranges to suit your sheet). Of course, the brackets indicate that this is an array formula so when you enter it you need to press CNTL-SHIFT-ENTER.

{=LOOKUP(2,1/(C6:C15=C4),D6:d15)+1}