Baseball-Reference.com lists 350 instances of a player hitting a walk-off home run while trailing and his team down to its last out in the span for which it has regular season data (back to 1925).
I would say that the best batter-pitcher matchup among those was Frank Robinson versus Bob Gibson.
It came in the first game of a doubleheader on September 19, 1964. Both teams were chasing the first-place Phillies (who lost that day on a steal of home!) and in the end, each team would win once.
The Cardinals probably should have swept though. They led 5-0 after three innings with Gibson pitching. Perhaps Gibson was winded by the three doubles he hit, but he was unable to hold the lead. Deron Johnson’s three-run home run in the sixth inning cut the Cardinals lead to 5-3. Marty Keough’s home run in the eighth made it 5-4. The Cardinals failed to score despite loading the bases in the ninth inning, which gave the Reds a chance at a comeback.
Give Cardinals manager Johnny Keane credit for trusting Gibson, who allowed a double to Gordy Coleman to lead off the ninth. A sacrifice by Pete Rose(!) moved pinch-runner Tommy Harper to third. Chico Ruiz struck out looking, but Vada Pinson walked on a 3-2 pitch (newspaper reports described it as “disputed”).
Robinson was 0-for-4 in the game and had struck out representing the go-ahead run in the seventh inning. He homered on Gibson’s first pitch to win the game.
Final score: Reds 7, Cardinals 5.
A couple of postscripts:
Robinson had a lot of big hits that season (he finished fourth in the MVP voting) and a lot of big hits in his career. In fact, Robinson is the only player in Baseball-Reference’s 350-home run data set to hit three walk-off home runs with his team trailing and down to its last out. This was the first of the three.
Just shy of a month later, Gibson found himself on the mound again trying to gut his way to the finish. This would also be a game with a final score of 7-5, though the result was different and slightly more important. It was the clinching game in the Cardinals’ World Series triumph over the Yankees.
Further reading: The website “Retrosimba” did a great review of Frank Robinson’s career vs the Cardinals here