The 1969 Mets. The team had a history of poor performance (in their nine-year history they had never finished better than 2nd-last in the National League) and limped off the line in 1969 with an 18-23 record. Then suddenly turned things around to crush all competition and become the first expansion team to win the World Series. (Also didn't hurt that the Chicago Cubs, who had been leading the NL for most of the season and had an 8 1/2 game lead in mid-August, suddenly choked.) In the film Oh, God!, George Burns, as God, states that his only miracle since the Red Sea had been the 1969 Mets.
The 2004 Red Sox in the 2004 ALCS against their hated rivals, the Yankees. They were down 0-3 in the seriesnote a series deficit which no team in baseball history had ever overcome to this point and it was Game 4 with one inning from being swept, they flipped the switch and got dangerous. It wasn't just one person, it was EVERYBODY from Keith Foulke to Derek Lowe to Bill Mueller to Dave Roberts...heck, even Curtis Leskanic!! Everybody got dangerous and the team overcame the deficit and won the ALCS en route to their first World Series title in 86 years.
Closers and bullpens in general are this in modern baseball. Against certain teams (the 2014-2015 Kansas City Royals come to mind), once opposing teams get to the seventh inning they can't really score any more runs because the trickiest and hardest-throwing pitchers are in the bullpen. Pitchers like Chien Ming Wang who have tricky windups or timing that would get figured out in a long game flourish for single innings out of the 'pen, as do players like Kelvin Hererra who can throw 100+ mph but wouldn't last for more than an inning or two. Bullpens are no longer "in case the starter can't finish the game", and more "in order to lock down the game when the team has a lead in the last couple innings".
Jose Ramirez of the Cleveland Indians. Initially a shortstop prospect, Ramirez had an underwhelming rookie campaign in 2015 and got replaced and relegated to bench play thanks to superstar Rookie of the Year candidate Francisco Lindor. So over the 2015-2016 off-season, he reinvented himself as an infield-outfield utility player and focused a lot of effort on his batting to become almost a living incarnation of this trope for the Indians. His batting average with runners in scoring position has been nearly or greater than .400 since the start of the 2016 season, and he is among the top five players in the American League in all clutch batting categories. He also improved his fielding ability so much that as of August 6th, 2016, he became the Indians primary third baseman, and has held the position down admirably.
During Super Bowl XXII, the Redskins were being completely outplayed by the favored Broncos, all with starting QB Doug Williams playing injured after being sacked in the first quarter. With the Redskins down 10, and all momentum in the Broncos' favor, Williams and the Redskins exploded for 35 points in the 2nd quarter, where even little used RB Timmy Smith reeled off a 58-yard TD run, while Williams threw all 4 of his own TD passes to three different receivers, all the while showing exactly why he and the Redskins belonged in the Super Bowl in the first place.
A similar feat would be achieved in Super Bowl XLIV, when the underdog Saints team found themselves down 10-0 to Peyton Manning and the Colts early in the game, only to outscore the Colts 37-7 over the rest of the game, including an interception returned for a game-clinching touchdown at the end of the game, also known as the point where Peyton Manning was guaranteed to lead his team from the brink of defeat if necessary, as he had done in almost every game that season. This and the aforementioned Super Bowl XXII were the only two times in NFL history that a team was able to rebound from a double digit deficit to win the Super Bowl.
During their 2008 season, the Arizona Cardinals, playing in the then-notoriously weak NFC West division, were statistically guaranteed entry into the playoffs despite a mediocre record. Meanwhile the New England Patriots, despite a superior record of 11-5 and a 47-7 victory over the Cardinals in week 16, didn't make the playoffsnote The Miami Dolphins and Baltimore Ravens also went 11-5, and due to both having better records within their respective conferences qualified over the Patriots, making the Pats the first 11-5 team to miss the playoffs since 1985. The Cardinals were harshly criticized, with some even suggesting the rules be rewritten to prevent such a thing from happening again. Then the Cardinals got dangerous, won their last game of the regular season and all three of their playoff games by convincing margins, made the Super Bowl for the first time in the franchise's history, and came within a couple minutes of winning.
The 2010 Stanley Cup Quarterfinals. The number 1 seed Washington Capitals and the highest scoring team in the league against the number 8 "we barely made it to the playoffs" Montreal Canadiens. With the Capitals up 3-1 by game 5, there was an obvious winner. And that's when Jaroslav Halak got dangerous. Over the next three games, Halak faced 134 shots... and let in a total of three. For reference, most games, a goaltender will face around thirty shots. Halak faced an average of almost 45, and in one game stopped 53... and won. His goaltending has been largely attributed as the reason for one of the biggest upsets in NHL history.
The 1980 USA Olympic hockey team. They trailed in every single game they played in the tournament and did not lose a single one, tying their first game against Sweden and won every game after that. And of course that included the "Miracle on Ice" against the overwhelming favorites from the Soviet Union, where, yes Al Michaels, we did believe in miracles.
The New York Giants, a consistently mediocre team with a consistently mediocre quarterback (Eli Manning) constantly in his brother's shadow have won two Superbowls, both of them over the nigh unbeatable New England Patriots. The second time they not only beat the Patriots, they ruined what had, up to that point, been a perfect season.
The Seattle Seahawks, another mediocre team, managed to fight their way to the 2014 super bowl and then utterly crush their opponents, the Denver Broncos. The final score was 43-8.