A brace from Rodrygo and a Karim Benzema penalty, all scored from the 90th minute on, gave Carlo Ancelotti's side a 3-1 win after extra time at the Bernabeu on Wednesday to send them to Paris with a 6-5 aggregate triumph in a semifinal not soon to be forgotten.
Madrid trailed 1-0 on the night and 5-3 on aggregate heading into the 90th minute after Riyad Mahrez's 73rd-minute goal looked set to send City to their second successive Champions League final. But, as dramatic comebacks against Paris Saint-Germain and Chelsea have already shown in this season's competition, Madrid are never to be counted out.
And they saved their most incredible feat for this night. After coming off the bench, Rodrygo scored in the 90th and 91st minutes to level the aggregate score and send the Bernabeu crowd into bedlam and the semifinal to extra time. With City shaken, Benzema then won and scored a penalty in the first five minutes of extra time to give Madrid a lead they would not relinquish.
As they seek a record-extending 14th European Cup, Madrid will now take on six-time winners Liverpool in the final at the Stade de France later this month. No one will be writing them off.
"Incredible," Madrid's Casemiro told Movistar Plus afterward. "There's no better feeling, no way to explain this, how difficult it was to get here. The best is yet to come, the most important [thing] is yet to come, but we have to enjoy the moment. ... We're in the final, but the most difficult is yet to come.
"The great virtue of this club is never giving up, fighting to the end."
For much of the night, there was little sign of the drama that was to come.
In sharp contrast to last week's breathless contest in Manchester, the second leg of the semifinal was largely a much more cautious affair. City, boosted by the defensive returns of Kyle Walker and Joao Cancelo, were largely happy to try to contain a Madrid side that featured the return of midfield enforcer Casemiro.
Still, spurred by a fired up Bernabeu crowd, Madrid began both halves strongly and had chances to level the tie.
Benzema, the competition's top scorer this season, uncharacteristically headed over from the center of the box just four minutes in. And Vinicius Jr. missed an even clearer opportunity in the opening moments of the second half when getting his finish all wrong after a rehearsed kickoff routine gave him a clear sight of goal from six yards.
Madrid looked like they would be made to pay for that profligacy. Mahrez, who had missed some big chances of his own a week ago, fired an emphatic near-post finish past Thibaut Courtois following a flowing counterattack to seemingly send City to Paris.
There was little in the following 15 minutes to suggest Madrid had another epic comeback in their locker. Indeed, City were inches from putting the result beyond doubt when substitute Jack Grealish beat Courtois only to see Ferland Mendy desperately clear the ball off his own goal line.
Instead of the icing on the cake, it was just a precursor to the remarkable next few minutes.
Rodrygo's opening goal, when he got to a Benzema pass ahead of Ederson, was Madrid's first effort on target on the night. His second, headed home from a Dani Carvajal cross, made it not only the first time that City had conceded two goals in the 90th minute or later but the first time they'd conceded goals in consecutive minutes since Pep Guardiola took charge.
From that point on, there looked to be only one winner. And that theory was cemented when Ruben Dias brought down Benzema in the City penalty area in the opening minutes of extra time.
"This team, this club is capable of anything, we believed until the end," Courtois said. "Mendy made a key save on the line, I did, too, we made it 1-1 and then 2-1 and they were dead. We were better in extra time. We had to defend well, and we did it. Incredible."
As Madrid now look forward to a record 17th Champions League final and Ancelotti to an unprecedented fifth final of his own, City's agonizing wait to be crowned champions of Europe goes on for at least one more season.
"We were close. We were close. But in the end we could not reach it," Guardiola told BT Sport.
"We didn't play our best, but it is normal, a semifinal, the players feel the pressure. Football is unpredictable, it is a game like this. We have to accept it."
With the final set, Caesars Sportsbook installed Liverpool as a (-195) favorite over Real Madrid (+155) in a matchup that looked extremely unlikely late in Wednesday's semifinal.
Real Madrid were as long as 45-1 to come from behind against Man City at Caesars Sportsbook. And one bettor saw the remarkable ending coming.
Caesars reported taking a $1,000 bet on Real Madrid to win the match in regulation just 15 seconds prior to Rodrygo's first goal. Rodrygo's second goal, which gave Madrid a 2-1 lead at the end of regulation, earned the bettor in Nevada a net $45,000 payday.
Information from Reuters was used in this report. ESPN's David Purdum also contributed to this story.