Avalon took care of your AGP Q's, so I'll try to help you find a card. I'd recommend either a
$112 9600 Pro or a
$173 9800 as the best values in their performance ranges. Still, if you have a slow system, some game may benefit more from a CPU upgrade and a $110 video card than just a $175 video card alone--not many, but some. You can see the performance difference between the two cards in recent AT benchmarkfests, and you can also take a look at XbitLabs' Winter Roundup, TomsHardware's VGA Charts III, and NordicHardware's 12 Card Roundup.
Comparable nV cards would be the 5700 and 5900XT. My preference would be ATi for DX9 performance and AA quality, but you may prefer nV for perhaps better drivers (mainly in terms of dual-monitor options) and a better (and slower) AA mode (4xS) for older games. The 9600P/9800 should run Half-Life 2 better, and the 5700/5900XT may run Doom 3 better. Choices, choices.

Seriously, both HL2 and D3 are probably months away, so I'd focus on which card will perform best in your system with your current and near-future games.