City led 1-0 on the night at the Santiago Bernabeu and 5-3 on aggregate heading into the 90th minute before two goals in 88 seconds from substitute Rodrygo sent the game to extra time.
Karim Benzema then slotted home the winner from the penalty spot to send the 13-time winners into the final against Liverpool on May 28 in Paris.
City on the other hand are still waiting to be crowned European champions despite the billions invested by the club's Abu Dhabi-based owners over the past 14 years.
Guardiola's men were left to rue a series of missed chances both in the first leg at the Etihad and after Riyad Mahrez had fired them into the lead 17 minutes from time.
"We didn't play our best, but it is normal, in a semifinal, the players feel the pressure and want to do it," Guardiola told BT Sport.
"Football is unpredictable, it is a game like this. We have to accept it."
Madrid also needed comebacks at the Bernabeu to see off Paris Saint-Germain and Chelsea in the previous two rounds.
But the Spanish champions had not managed even a shot on target until Rodrygo turned the tie by flicking home Benzema's cross.
The Brazilian then rose highest to power home a header in the first minute of stoppage time.
"After we scored the goal we were better. We found out tempo and our game and the players were comfortable," added Guardiola.
"It is not like in the last 10 minutes they attack and attack and you suffer. It didn't happen. They put a lot of players in the box, with (Eder) Militao, Rodrygo, Vinicius (Junior), Benzema and they put in crosses and scored two goals."
City need to get another painful European exit out of their system quickly if they are to avoid ending the season without a trophy for the first time in five years.
The English champions lead Liverpool by a point at the top of the Premier League with four games remaining.
"Now we need to process that and come back with our people at home and the last four games we have," said Guardiola, whose side host Newcastle on Sunday.