In your last sample workbook... the names in column B of Sheet1 do not appear in Sheet3, column Y.. that is why you get 0.

Formula adapted to Sheet3, is

=IFERROR(MAX(IF(Sheet3!$Y$2:$Y$43=B2,Sheet3!$T$2:$T$43)),"")

confirmed with CSE... put a name in B2 that is actually part of Sheet3!$T$2:$T$43 and test...