Intel Core 2 CPUs are still available, for instance, but they've been around for a long time, no longer represent a good deal and unless you're working with an old motherboard, are probably best avoided.
Instead you'll be considering a Core i3, Core i5 or Core i7 processor, which will deliver budget, mid-range or high-end performance, respectively. Except, of course, it's not quite as simple as that, because these CPUs now come in two main flavours.
The mainstream Core i3, i5 and i7 processors are available in a Socket LGA1156 package (this defines how the CPU connects to the rest of your system, and so means you'll need a Socket LGA1156 motherboard as well). This doesn't represent the very latest in Intel technology, but it's relatively reasonably priced, and there are plenty of compatible motherboards around at all price levels.
The latest Core i3/ i5/ i7 processors, code-named Sandy Bridge and available in an LGA1155 package, are rather more interesting. Not only are they up to 40% faster than their predecessors, but they also come with an on-board graphics chip, so if you're not looking for much in the way of video performance then you probably won't have to buy a graphics card.