Just off the top of my head, I'd agree about Bioshock 1 being a special game. It's creepy, it's fun, it's not too challenging but it can kick your ass at times, and I found the story to be very interesting and really well revealed. The atmosphere is unique and very well done as well, probably the best part of the game.
Orange Box is also exemplary, particularly for Portal which is in a class of its own. It's no big budget game at all, but more of an experiment in FPS-with-a-different-kind-of-gun turned into a really well paced puzzler, with a hilariously engaging story that is revealed in unusual ways. It has unquestionably one of the most satisfying endings I've experienced in a game, and many parts are exhilarating with all the flying around. Probably wouldn't take you more than eight or ten hours total, if that, to play through it and the 'developers commentary' version of each chapter is very interesting to check out. Also on that disc is Half Life 2, which is the big budget behemoth, and while uneven at times, the beginning is extraordinary. I personally really enjoyed the middle parts but I can see how many found that section bland and boring, but certain areas of the game are just classic and should be played through. It's top notch FPS, no doubt. Episode 1 is pretty poor, IMO, but Episode 2 is just as good as HL2 itself, albeit very short. Plus HL2 has both Louis Gosset Jr and Benson in the cast. Benson! (Robert Guillaume, of course).
Left for Dead, I'd stay away from. It's pretty awesome and I have great memories of playing it, in fact I miss it quite a bit, but there's no point unless you have two or three good buddies to play it with online. It's no good for single player.
I really enjoyed and played the hell out of Red Faction Guerilla. It's a 3rd person shooter with some driving a là GTA but with crazy destructible environments and after a pretty slow start I found it to be tons of fun and rarely frustratingly hard.
I also played the crap out of Just Cause, the original, which I was probably one of about ten in the world to complete. It's also a GTA style, open world 3rd person game, and while it is very uneven and can drag, there was something about the openness and the craziness of its vertical acrobatics and flying around that made it crazy fun for me and kept me coming back. There's just nothing else like taking a jet up to its highest altitude, jumping out, and free falling all the way down to the surface only to pull out the parachute at the last instant. Exhilarating! While I didn't play Just Cause 2, all accounts are that it's more of the same, only better in every way.
Mass Effect and Mass Effect 2 have been mentioned, but I just don't see you getting into them at all, seeing as they are scifi games and pretty wonky RPGs.
I actually enjoyed Splinter Cell Double Agent's single player quite a bit more than I had other SC games, plus if you get that one it might give me a good excuse to try to rally the troops for another trip down memory lane with that game's totally unique and unparalleled asymmetrical multiplayer.
The original Crackdown was much like crack. Tons of fun, lots to do; especially fun was hunting down the orbs you could collect. It's also got a wonderful vertical element and eschews difficulty and the frustrations of challenge for open world fun and giving you that superhero feeling. I never played the newer one, but the first is remembered pretty fondly by most, I'd imagine.
Sports-wise I can't recommend any older, cheaper versions over their current counterparts, but I do hope you'll consider joining Danimal's DSPFL Online Franchise in Madden. We are almost near the end of season one so you can jump in pretty soon in the offseason if not right away. Then, if you take the Bills, you can trade me Fitzpatrick on the cheap!
