Atletico will play in the showpiece match for the second time in three seasons against either city rivals Real Madrid, who beat them in the 2014 final, or Manchester City, with the second semi-final scheduled for the Bernabeu on Wednesday.
Bayern's exit ended their hopes of claiming a treble with Atletico going through to the May 28 final at the San Siro in Milan after winning the first leg 1-0 last week.
The Bavarians went ahead with a Xabi Alonso free kick just past the half-hour and could have scored again three minutes later but Thomas Mueller's penalty was saved by Jan Oblak.
Atletico levelled through Frenchman Antoine Griezmann's seventh goal of this season's competition in the 54th, meaning Bayern would need at least two more goals to progress.
Bayern's Poland striker Robert Lewandowski set up a dramatic finale with his 74th minute goal before Atletico's Fernando Torres had an 85th minute spot kick saved by Manuel Neuer.
But it made no difference in the end as Bayern suffered a third straight elimination by a Spanish team at this stage in their three seasons under Pep Guardiola.
"They were better, but we scored an away goal, which was important. We need to keep grounded and first of all think of the Liga before the final," said Griezmann, whose side are level on points with leaders Barcelona in the Spanish title race.
"We're delighted. It was difficult, a really hard game, we had a great opponent in front of us and we had to both defend and try to score. That's what we did," he added.
Bitter pill
Guardiola, in his final Champions League home game at Bayern before his move to Manchester City for next season, started with defender Jerome Boateng, back from a three-month injury break on Saturday, and Franck Ribery, himself back from injury.
With a newly-discovered intensity, the Bavarians were dominant in the first half, successfully navigating through the Spaniards' high pressing game and grabbing their first chance with a Lewandowski header in the 12th minute.
"We are a bit lost for words," said Neuer. "We played great football today, did everything we set out to do but it is bitter not to be travelling to Milan for the final."
Atletico keeper Oblak was kept busy in the first half, with the tables turned and Bayern doing the pressing -- having 12 shots compared to Atletico's two by the half hour.
Alonso finally got the hosts a deserved lead in the 31st minute, his low free kick clipping Atletico's Jose Maria Gimenez to cancel out Atletico's first-leg advantage.
Bayern pushed for a second goal and thought they would have it when Javi Martinez was brought down by Gimenez in the box to earn a penalty three minutes later.
But Oblak saved Mueller's spot kick and Atletico stunned the home crowd after the break when Griezmann linked up with Torres and charged through to beat Neuer with a fine finish.
With Bayern now on the backfoot and needing at least two more goals to advance, Atletico grew bolder and Juanfran's shot on the hour sailed narrowly wide.
Lewandowski put Bayern back in front on the night with a close-range header to spark a dramatic finale but they could not find another goal and their Champions League hopes were over.