This formula below does an ok job but but it doesn't have the ability to identify the las row of the Final Report tab. I would manually have to adjust the "119", which defines the last row, in order for it to work. I did try to define the name range-=OFFSET(FINAL REPORT!$A$1,0,0,COUNTA(FINAL REPORT!$A$2 REPORT!$A:$A),1) but no luck.

=INDEX('FINAL REPORT'!C:C,MATCH($A$3,'FINAL REPORT'!$A$2:$A$119,1)+MATCH($B3,INDIRECT(CONCATENATE("'FINAL REPORT'!B",MATCH($A$3,'FINAL REPORT'!$A$2:$A$119,0)+1,":B700")),0),1)

TIA,
excellicious