Dortmund hang on to reach Wembley final
The German side, who will play in their second final after winning the competition in 1997, survived early pressure from a fired-up Real in the last-four second leg clash before wasting several chances to kill off the home team.
Real, chasing the 10th European crown that has eluded them since 2002, looked down and out before substitute Karim Benzema struck in the 83rd minute and Sergio Ramos smashed in a second goal five minutes later to given Jose Mourinho's side hope.
With the raucous crowd on its feet and willing their team on, the Real players desperately sought the goal that would have taken them through but Dortmund held out to set up a clash with Bundesliga rivals Bayern Munich or Barcelona.
Bayern have a 4-0 advantage over the La Liga leaders after their stunning victory in Munich last week.
Dortmund's success put them into their first European final since the 2001/02 season, when they lost the UEFA Cup showpiece to Feyenoord, while Real are the only team to be eliminated in the Champions League semi-finals for three years in a row.
"It was tight at the end," Dortmund coach Jurgen Klopp said in a television interview.
"We had our huge chances before that. Had we scored then it would have been over. Overall we played too little football but we won 4-3 over two games and deserve to be in the final."
Real's reverse is also a huge setback for their charismatic coach Mourinho, who was trying to become the first man to win the Champions League with three different clubs after triumphs with Porto in 2004 and Inter Milan in 2010.
It is the fifth time the Portuguese has been knocked out in the last four of Europe's elite club competition in seven semi-final attempts.
In typically outspoken fashion, Mourinho complained that Dortmund defender Mats Hummels should have been sent off for handball in the second half but praised his players for "giving their all".
"We had opportunities to score much earlier and then we obviously would have had a better chance," Mourinho, who has refused to commit to staying at Real beyond the end of this season, told Spanish TV.
"Near the end we almost got there but almost does not exist. Either you make it or you don't and we didn't make it," he added.
"We have had three strong seasons with almost, almost, almost. I hope that in the future, and obviously that it happens soon, we can leave the almost behind and get to the final."
With Spanish King Juan Carlos watching from the VIP tribune and chants of "Yes we can!" echoing around Real's giant arena, the home supporters unveiled an enormous mosaic depicting the Champions League trophy before kick-off.
The electric atmosphere appeared to energise the players and Real took the game to Dortmund early on, Gonzalo Higuain drawing a smart save from Roman Weidenfeller in the fourth minute.
Dortmund looked nervous but were also managing to get forward and Robert Lewandowski, who scored all four of their goals in last week's 4-1 first-leg victory, volleyed straight at goalkeeper Diego Lopez in the 13th minute.
Real's Cristiano Ronaldo had an almost identical chance moments later which Weidenfeller again did well to keep out.
After Dortmund playmaker Mario Gotze was forced off injured in the 14th minute, his Germany team-mate Mesut Ozil had a golden chance to open Real's account when he was clean through on goal but his low strike flew narrowly wide of the post.
With Dortmund pressuring the Real players and denying them time on the ball, the home support began to get restless as the chances for the hosts started to dry up.
By half-time the Dortmund fans were starting to make themselves heard and the "Yes we cans!" from the frustrated local fans seemed increasingly half-hearted.
As the second half began, Mourinho cut a worried-looking figure and striker Benzema immediately began to warm up.
Dortmund had two chances early in the second period and both fell to Lewandowski. The Poland forward struck a wild shot over the bar in the 49th and then raced clear onto a Marco Reus pass a minute later but smashed his effort against the crossbar.
Mourinho made a double substitution in the 57th minute when Benzema and Kaka replaced Higuain and Fabio Coentrao but it was Dortmund who looked more threatening by that stage.
Lopez somehow managed to scramble across to deny Dortmund's Ilkay Gundogan who had the goal at his mercy and Lewandowski's shot was blocked by Michael Essien in the 76th.
Ozil crossed for Benzema to give Real a sliver of hope seven minutes from time and Ramos struck through a crowd of players to set up a frantic ending but it was all too late.
"They really put pressure on us and we let in two goals towards the end and then it really became tense," Dortmund substitute Kevin Grosskreutz, who replaced Gotze, told Sky Television.
"But we have a great squad, super fans such a great club we just deserved to go through."
Ramos told Spanish broadcaster TVE: "It's a shame. You can lose and football is like that but when you lose it should be with this version of Real Madrid.
"If we had come out playing like we did tonight in Dortmund things would be very different.
"We had a lot of chances in the first half to take the lead that we didn't take and in the end we didn't make it."