Real's dream of winning a 10th European title at their Bernabeu stadium in May was shattered by Miralem Pjanic's 75th-minute strike, which secured a 1-1 draw for visiting Lyon in the last 16 second leg after Cristiano Ronaldo's goal in the sixth minute.
"You can say it's a great achievement. We escaped the worst possible scenario in first half. Hats off to the players who gave all they had tonight. We played a superb second half and I think we deserved it," Lyon coach Claude Puel told TFI channel.
Real president Florentino Perez spent close to a quarter of a billion euros ($340 million) in the summer in a bid to end the club's Champions League drought and their latest failure raises fresh questions about the future of coach Manuel Pellegrini.
Real went top of La Liga ahead of champions Barcelona on goal difference at the weekend but the domestic league is now their only chance of silverware this season.
Real midfielder and captain Guti said the club's exit from the competition was even more painful this year given that the final was in their own stadium and criticised some of his team-mates for selfish play.
"We have to be more of a team and not play so much as individuals," he told Spanish television.
"We didn't come out and perform as we should have done. It's a shame and now we have to think about the league which is all we have left. All these years have been painful and this one too, much more," he added.
A fired-up Real started the match strongly, winning the ball immediately from the Lyon kickoff, and goalkeeper Hugo Lloris has to sprint off his line to deny Kaka.
The rampant home side were ahead soon after.
Guti lofted the ball to Ronaldo, set to lead Portugal's attack at this summer's World Cup, and the competition's top scorer this season took his tally to seven with a low finish that flew between Lloris's legs.
Gonzalo Higuain came desperately close to a second in the 26th when he rounded Lloris and had the empty goal in his sights but his angled shot bounced away off the inside of the post.
Two minutes later the Argentina striker picked up a loose ball in the area and fired a shot toward the bottom corner but Lloris palmed the ball away with a superb fingertip save.
Real were far less dominant after the break and Lyon began to create chances of their own.
Substitute Maxime Gonalons headed narrowly over, Sidney Govou miscued an effort into the crowd from a good position and striker Lisandro Lopez stung the hands of goalkeeper Iker Casillas from distance.
Pjanic stunned the home fans when he picked up a loose ball in the Real area with 15 minutes left and hammered in a volley.
As Real poured forward and gaps opened up in their defence, Lopez and Cesar Delgado were both clean through on goal but failed to finish.
Tempers frayed at the end, substitute Raul and Lyon captain Cris clashing, and the final whistle was greeted with a mixture of stunned silence and subdued whistling from the Real fans and wild euphoria from the Lyon faithful high up in the stands.
Olympique Lyon sink Real Madrid in first leg
Real Madrid win thriller, go top of La Liga
Champions League Images: Rooney stars for Man U
Lopez hits three but Lyon lose at Lille
Real Madrid held at Osasuna