Goode's late intervention means Leicester head to Twickenham
Defending champions Leicester fought back from a 12-3 half-time deficit to earn a Premiership final against Wasps.
Four Ryan Lamb penalties put Gloucester in control before Alesana Tuilagi's try gave Leicester some hope.
James Simpson-Daniel's try restored the hosts' nine-point lead but Leicester went in front through Aaron Mauger's converted try and an Andy Goode kick.
Willie Walker's drop-goal put the hosts in front but Goode landed a 78th-minute drop-goal to see Leicester home.
It was an extraordinary fightback by Leicester, who despite being reigning Premiership champions went into the game as big outsiders.
Whereas Gloucester topped the regular-season table for the third time in six years, Leicester required a last-gasp Tom Varndell try against Harlequins just to scrape into the play-offs in fourth place.
Corry hailed a great team performance from the Tigers
No away side had ever won a play-off semi-final, and when Gloucester dominated the first 40 minutes it looked extremely unlikely that Leicester would break the mould.
Gloucester could not cross the Leicester line in the first half, but that was down to a combination of brave defence and cynical play by the visitors.
Two Lamb penalties to one from Goode - who also missed a simple effort - saw the hosts lead just 6-3 after quarter of an hour.
But Leicester lost Jordan Crane and Harry Ellis to yellow cards and two further Lamb penalties gave Gloucester a 12-3 advantage at the break.
After their 44-16 battering by Leicester in the 2007 final, Gloucester had added plenty of brawn in the likes of Akapusi Qera and Lesley Vainikolo so they could compete on level terms with the powerful Tigers.
It looked as though the new-look Cherry and Whites were going to avenge last year's defeat, but Leicester dug deep to produce one of the most compelling halves of rugby seen all season.
Tuilagi added another try to the brace he scored in the 2007 final
The two fly-halves kicked a penalty apiece before Tuilagi, who destroyed Gloucester in last year's final, battered his way through three tackles to score in the corner, with Goode converting.
That cut the gap to just two points but Gloucester hit back when the superb Qera sent Simpson-Daniel, who played outside centre in place of the absent Mike Tindall, cruising over.
Lamb added the extras to make it 22-13 with an hour played.
The hosts seemed poised to stretch clear but panic in defence after a poor Lamb kick saw Iain Balshaw throw a pass onto his own posts and Mauger pounced to score as simple a try as he will ever collect.
Goode's conversion cut the gap to two points and he landed a 67th-minute penalty to put them 23-22 ahead, the first time they had been in front all game.
It was a remarkable recovery but a Titanic struggle kicked up a gear as the game approached the final whistle.
With five minutes to go the hosts worked a position for Walker to nudge them back into a two-point lead and send the Gloucester hordes at a packed Kingsholm delirious, but Leicester were not going to be denied an historic victory.
They engineered a chance for Goode and the fly-half drilled his drop-kick between the posts to send the champions to Twickenham to meet long-term rivals Wasps for the right to be crowned 2008 champions.
Leicester captain Martin Corry: "We just stuck at it, especially when we were down to 13 men. When you have someone like Goodey on your side and a great finisher like Alesana, you always have a chance.
"In all fairness our consistency and form throughout the campaign hasn't been up to much, and we didn't produce our best rugby today. It wasn't a complete performance but the lads showed great fighting spirit.
"We were more dogged than anything else. It showed how much this competition means to us."
Coach Marcelo Loffreda on his own future: "Six months is not too long - I don't even know some of the players too well yet. But we have good communication and they are beginning to understand what I want.
"I don't know what is going to happen with me next season but I will be very busy now preparing for the next game."
Gloucester boss Dean Ryan: "I don't think it was about temperament, I think we made stupid choices. I thought we were lacking 'Tins' a little bit.
"He does a lot of our thought processing and we are just not smart enough when he is not there.
"For the last few weeks we have been very smart and the best side around. We have taken Wasps away and beaten Bath at home, and we could and should have beaten Leicester today."
This page is best viewed in an up-to-date web browser with style sheets (CSS) enabled. While you will be able to view the content of this page in your current browser, you will not be able to get the full visual experience. Please consider upgrading your browser software or enabling style sheets (CSS) if you are able to do so.