I think Psychonauts was the last game where I truly felt something. It's kind of that same feeling when watching "Edward Scissorhands" for the first time when I was like 8 years old.
The first portal game was great too. haven't started the 2nd one yet so can't comment on it just yet..
If you don't mind indie point and click adventures, 'The Whispered World' and 'Machinarium' are truly beautiful games. The ending to Whispered World is one of those really memorable moments that will stick with you. You might find these types of games slow and boring at first, but it's good to mix things up a bit, play some slow "artsy" games if you want to be inspired, then some generic shooter when you want some more action, etc..
an older game that was extremely memorable to me was the beginning 20 minutes of 'Prey'. The rest of the game is just mediocre mindless shooter so I don't really recommend it for the gameplay alone, but I'd pay 10 bucks for the game just to experience the first 20 min, it's totally worth it. I got into the game not knowing what's gonna happen and it totally blew my mind out.
For games with current gen graphics, the closest one to inspiring me is probably the Mass Effect series, so far I think it is the best representation of living in a world among other alien species in a video game. All the background story and politics that go with it makes you feel and understand the world enough to care about what's going on.
some other older games that stuck with me: Legacy of Kain/Soul Reaver(story), Hitman(gameplay), No One Lives forever(humor), Abe's Oddyssey /Exoddus(characters), Day of the Tentacle(humor), etc..
-