Going into the game level after a first leg in which they should have put the tie to bed, Wenger's men looked the better side for much of the 90 minutes.
But one lapse in concentration on the brink of half time allowed Diego Costa to score the only goal of the night, putting Atleti through to the Europa League final with a narrow 2-1 aggregate win.
"We feel over the two legs there was enough to go through," Wenger told BT Sport after the match. "If you look at the two games, we didn't manage the important moments well.
"We conceded a stupid goal with one minute to go on the counter-attack, they are vital moments in a game of that stature. They played with a bit of fear and once they were 1-0 up they had a little cushion, that's why that goal was absolutely vital tonight."
And on not being able to lift a European trophy as Arsenal manager, a visibly emotional Wenger said: "It's very sad, very, very, very sad. I'm very sad tonight but unfortunately our game is like that.
"It can be very cruel, it can be kind but the suffering is very strong tonight."
To reach the final, Arsenal would have to do what no other visiting team at the Wanda Metropolitano had done in the last three and a half months: score a goal.
Atletico had not conceded in their last 11 home games and had only conceded nine goals in their shiny new stadium all season, six of those coming from crosses.
It was a weakness which Arsenal repeatedly exploited in the first leg, with Alexandre Lacazette eventually getting his goal with a header from a delivery out wide.
It was a tactic Arsenal again turned to in Madrid, though early crosses in promising positions from Danny Welbeck and Hector Bellerin lacked any quality.
Another early cross, whipped across the face of goal by Lacazette after good play from Mesut Ozil, tested the early concentration of Atletico's back line.
The first real opening came after six minutes as Diego Costa latched onto a through ball and charged through a feeble challenge by Nacho Monreal, but his shot was skewed horribly wide with Antoine Griezmann better placed in front of goal.
Arsenal were then dealt a sizable blow as captain Laurent Koscielny was stretchered off in agony after twisting his ankle awkwardly in the turf.
It was later confirmed that he had suffered a ruptured Achilles, ending his hopes of playing in what would surely have been his last World Cup.
His replacement Calum Chambers, almost a decade Koscielny's junior, provided far less European experience but slotted seamlessly into Arsenal's back line, corralling Costa and Griezmann comfortably.
The opening 25 minutes were tense and nervous, the two former heavyweight champions tentatively sparring, but the game came to life soon after as Arsenal began to exert an authority on proceedings.
Lacazette was played through on goal but a poor second touch saw the ball get away from him when he really should have tested Jan Oblak in the Atleti goal.
Ozil drilled the follow-up back into the box where Aaron Ramsay was waiting to turn the ball home but Diego Godin, as he has done on countless occasions throughout his career, put his body in the way to block the ball.
As the first half drew to a close, there was a palpable frustration growing inside the Wanda from both Atletico's fans and players, who perhaps felt surprised they weren't having things go their way.
Gabi and Costa in particular were persistently remonstrating with referee Gianluca Rocchi, while two Griezmann misses -- the second a difficult chance after a clever turn -- were met with the sound of 60,000 simultaneous groans.
But just as the first half felt like it was petering out, Arsenal found themselves a goal behind with less than 60 seconds of stoppage time remaining.
Chambers' defensive header was picked up in midfield by Griezmann and the Frenchman slotted a ball through to Costa -- lost by the daydreaming Bellerin -- who lifted it over David Ospina.
Diego Simeone, cutting a solitary figure up in the executive boxes after his sending off in the first leg, pumped his fists wildly as the rest of the Wanda Metropolitano finally came to life.
Costa, so often the scourge of Arsenal during his time in a Chelsea shirt, had once again returned to haunt Wenger with his fourth goal in seven games against the Gunners.
Towards the end of the first half, Arsenal were having little joy as they began attacking through Atletico's congested center.
Wenger's half-time team talk will have highlighted as much, as Arsenal again began focusing on Atleti's fragile wide areas when the sides emerged for the restart.
Welbeck's pace and Lacazette's clever movement were pulling Atletico's defense apart and only a cynical tackle by Diego Godin on the former prevented a dangerous counter-attack.
As the clock ticked past the hour mark, Arsenal, through Ozil's trickery down the left, began creating chances at will. It felt like a crucial period; now or never for Wenger in his final shot at a European trophy.
It was now 17 and a half hours since Atletico last conceded a goal at home, though they were perhaps now looking more likely than ever to let one in.
Atletico did, however, continue mustering chances of their own on the break. Costa went close first, before fabulous work from the Spaniard set up Griezmann but Chambers got across to make a superb block.
In truth, Arsenal's attacking threat waned considerably as the half wore on, perhaps demoralized in the knowledge their chance to level the match had been and gone.
It seemed fitting, then, that a tie in which Arsenal had flattered to deceive for 180 minutes, but eventually came up short in, spelled the end for Wenger in Europe.
At the full time whistle, Wenger, like most of his players, was crouched, dejected, on his haunches as Atletico's players began to celebrate wildly in front of their fans.
It will be another European final for Madrid's second club, their fifth in nine years, where they will meet Marseille on May 16 in Lyon.