I tend to play a LOT of Co-Op games these days, to the point that I can say I've most likely tried virtually every major Co-Op game currently on the market. Most of them are good for one reason or another but rarely does the game truly seem designed for two players. That said, some titles I've played over the last few years that's worthy of comment are:
HAWX - There are probably people who like this game. Personally, the game's mechanics were dull at best and the story was quite simply so terrible that any enjoyment I might have wrung from uninteresting mechanics was lost.
Army of Two - The base game is just a third person shooter. It isn't particularly interesting to play single player, but with a friend it can be good for a laugh. Until HAWX came around it had the single stupidest plot point I'd ever heard of in a game (An aircraft carrier gets hijacked. A ship with a crew of thousands that sails in a fleet that is capable of waging a successful war against most nations on earth without support was hijacked. Not sunk. Not destroyed. Taken over. Stolen.) It's worth a play if you can get it on the cheap. The same can be said of the sequel and for 50 Cent: Blood on the Sand.
Mercenaries 2 - the game's core premise is that they let you loose in a sandbox with a bunch of destructive toys and let you go nuts. This premise often doesn't work well in single player because the best toys are more trouble to use than they're worth. With a friend around on the other hand the game turns around entirely. The fun to be had here is generally finding a creative way to deal with a problem. Need to sink a ship but you're out of airstrikes? Steal a utility helicopter and a yacht. Fill the yacht with explosives and tow it over the target. Careful timing will turn your yacht into a massive bomb!
Gears of War - Everyone's already played this one so it isn't worth covering. It has the (now standard) series of set pieces with chest high walls where you'll engage in brutal close quarters gunfights. It's tolerable with the AI partner but legitimately improves with a second player. Also, it's one of the only games where I had more fun when I raised the difficulty level.
Resident Evil 5 - The game has terrible controls but luckily you're absurdly well armed and facing some extraordinarily thick foes. Single player is probably just shy of impossible to play thanks to how stupid your partner is. Multiplayer is entertaining enough for a run through the game. Maybe two if there is nothing else to play.
Splinter Cell: Conviction - the cooperative campaign is only a few hours long. It was entertaining but inside of the first week of having the game I had beaten both the single player, the cooperative portion, and gotten well and truly tired of the game. It honestly isn't worth picking up for cooperative play until the price drops considerably because you almost certainly won't be playing this game for long.
Rainbow Six Vegas/Vegas 2 - Again, everyone has already played this game I'm sure. It does better than most when it comes to making the second person feel like they have a purpose in teh game, but the actual core gameplay wears thin in a hurry for me. Still, I have put a few dozen hours into the game with friends, so I suspect that says something.
Ghost Recon Advanced Warfighter 1/2 - the same as above really. The pacing is terrible and the gameplay is often incredibly tedious. Still, it has offered up more than it's share of entertainment.
Lost Planet 2 - This is monster hunter with space marines, and it brings all the baggage inherent with it. Single player is often just tedious - where you try and wrangle your mush headed partners in order to kill an enemy with an absurd amount of health. The simple act of "shooting monsters" isn't terribly pleasing by default. In short, the game just generally suffers without additional people. When you add other players the game improves dramatically and becomes an experience worth the asking price. It still won't be a game that everyone will like however because the problems don't really go away - they're just less obvious when you add more players.
Red Dead Redemption - Suffers from a distinct lack of things worth doing in free roam. If you play alone with your friends, you'll find that having them along is often more of a hinderance than a help. If you play in an open game you'll find what the old west is like when leveraged as an open world griefing simulation. Single player is fantastic, but the multiplayer can't hope to hold one's attention for more than a few hours at best.
Doom - This is the first game I ever played across a network and the game that made me into a gamer. It's also 17 years old and not for everybody. Still, at ten bucks it's not a bad bargin, considering the amount of content present if you've never played the games before.
Doom 2 - same as above but with a second barrel on your shotgun. Also, enemies that burst into flame and hurt you for some reason.
Duke Nukem - same as above but with a distinct flavor of bombastic 80's action movie. Doom always trended towards the serious and scary (as scary is it could be with that kind of technology). Duke Nukem on the other hand was all about the one-liners. The game holds up better than Doom for most, thanks to the fact that many things we assume ought to be in an FPS were in Duke such as the ability to look up and down (it was the first game that really wanted you to use mouse look) or the ability to jump. World design is much improved, and when you're sent into a supermarket or movie theater it actually seems like the people building the levels knew what such things looked like.
There are others, but many of them are fairly old these days. With the exception of Red Dead and Lost planet, most of these games are incredibly cheap (>$30 in most cases).