Look up date, pull result from another column, and populate it in another sheet?

I'm trying to figure out how to look up 'yesterday' for each of the employees' spreadsheets, find the corresponding goal numbers, and populate them on the Leaderboard sheet. For example, on February 12th Bob was 4 tires ahead of his goal (February 11th shows 4) and I want that to populate on the Leaderboard sheet.

Technically the goal is 2 tires/day with a 5 day week, but some employees work Tues-Sat and some are Mon-Fri, so I think I have to make the goal 10/wk instead of 2/day. That will make the daily goal a tad off depending on the day, but I think it's something we have to live with.