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".
The 2016 Indians took this to another level. They had three strong pitchers in former starter Zach Mc Allister, slider specialist Jeff Manship, and rookie power pitcher Mike Clevinger. However, the meat of their bullpen was contained in "the Big Four". Closer Cody Allen has a really powerful and accurate upper-90's fastball and a nasty 12-6 curveball. Sinker specialist Dan Otero shocked everyone with his 1.53 ERA (#4 among relief pitchers with a minimum 40 innings) in a tremendous comeback after a disastrous 2015 with Oakland. Setup man Bryan Shaw was the workhorse of the bullpen in 2015 and 2016 both, racking up more innings than any reliever in the AL with his Cutter/Slider combination and high speed. However, when the Indians really wanted to get dangerous, they brought in big lefty Andrew Miller. A 6'7" pitcher with a broad sidearm motion, Miller packs a mid-high 90's fastball with excellent control. His biggest plus is his tremendous wipeout slider, a pitch so unhittable players routinely swing themselves clean off their feet trying to make contact with it. He's been compared to Hall of Famer "Big Unit" Randy Johnson with how overwhelming he is. The thing is, all members of the "Big Four" are capable of setup, closing, or middle relief, and all of them are unselfish and willing to pitch in any role Terry Francona asked of them, meaning that the Indians had a huge amount of options in the crucial late innings. All they really needed to get a victory was to bring in one or two of the "Big Four" in any given game. But when they used all four at once? Completely undefeated in 2016.
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.
As of 2017, Ramirez took his danger level Up to Eleven by becoming an All-Star starter and finishing 3rd in the American League MVP voting behind monster hitters Jose Altuve and Aaron Judge. Oh, and he was also a Gold Glove finalist. So much for a glove-first shortstop, prospect evaluators!
Hell, the 2016 Indians as a whole could be considered this. After a disappointing 82-80 season in 2015, the Indians came out in April with a 10-11 record, which had folks groaning. By the end of May, much the same with a 26-24 record, but they kicked it into total overdrive in June, going 22-6, snagging first in the AL Central from the Royals on June 4 and never letting go through the very end of the season. In addition to going 14-3 against divisional rivals the Detroit Tigers, the Indians also had the most walk-off wins in the MLB with 11, the longest winning streak in the MLB at 14 straight games from June 17 to July 1, tied for the best home record in the American League with the Texas Rangers at 53-28, and finished with the second best overall record in the American League at 94-67. They were also the most consistent team in the MLB, being the only team to not have a losing streak longer than three games for the entire season. Did I mention that they did this without their best hitter (Michael Brantley played in only 11 games and batted only .231 while battling a shoulder injury that would eventually take him out for the duration of the season), losing their primary catcher and having their backup rushed back from injury rehab, and while losing their second best and third best starting pitchers for significant chunks of the season? And then they went and came within a run of winning the World Series against a vastly superior and better-paid Cubs lineup. Yeah. Get dangerous indeed.
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.
The 1994-95 Houston Rockets may have been the defending NBA champions, but they struggled to the sixth seed in the playoffs that year (no team before had won the title with lower than the second seed since the league went to its current playoff format) and ended the regular season with three straight losses - two of which were to the Utah Jazz team they would face in the first playoff round. They then came back from down 2 games to 1 (in a best of 5) to beat Utah, then from down 3-1 (best of 7) to beat Phoenix before disposing of the top team in the West (San Antonio) in the West Finals and then the top team in the East (Orlando) in the NBA Finals for their second straight crown, prompting coach Rudy Tomjanovich to sum it up by saying "Don't EVER underestimate the heart of a champion!"