A 73rd minute goal at the Bernabeu from Riyad Mahrez looked to have put City on the brink of a place in the Paris showpiece.
And substitute Jack Grealish was twice within a whisker of doubling our lead, seeing a shot cleared off the line and graze the post.
But in a shattering finale, Real sub Rodrygo struck twice in 90 seconds of injury time to level the tie at 5-5 and send the match into extra time.
And a 94th minute penalty from Karim Benzema condemned City to a shattering 6-5 loss on aggregate.
It was a cruel loss after City had again impressed against the Spanish champions.
And asked to reflect on the result in his post-match press conference, the boss admitted it was a defeat that hurt given we were so close to reaching the final once again.
“It is tough for us. We cannot deny it. We were so close to arriving in the Champions League final,” Guardiola admitted.
“We didn’t play much good in the first half. We didn’t find our game, but it’s normal in this competition. The second half was much better.
“After the goal we controlled it. We found the game, we arrived at the byline. Jack (Grealish) helped us to control the game and unfortunately, he could not finish when we were close.
“Before the first goal [for Madrid] we had two chances, especially one so clear with Jack.
“In that moment we didn’t have the feeling we were in trouble for the way they attack. It didn’t happen. They score a goal at the end. They have done it many times in their history.
“They put a lot of players in the box. Four strikers plus Militao and they find the goals.
“I have had defeats in the Champions League. I have had tough defeats with Barcelona against Chelsea when both games were exceptional and we could not reach the final.”