Los Blancos came into the second leg of their semi-final tie trying to overturn a 4-3 deficit.
However, it was City who scored first at the Santiago Bernabeu, with Riyad Mahrez slamming the home ball at the near post.
But Madrid turned the game on its head with a quickfire Rodrygo double.
Karim Benzema then converted a penalty in extra time after he was fouled by Ruben Dias to ensure the LaLiga champions progressed with a 6-5 aggregate.
They will now face Liverpool in the final on May 28.