First, let's cover some basics. There are exhaust "kits" that include all needed parts, and are customized to fit YOUR particular year, make, and model, but there are also universal mufflers and pipes, which allow you to build your own set-up. Unless you're an experienced fabricator, I recommend staying with the vehicle-specific kits, for both cost and ease of installation.
Another thing to keep in mind is that almost every car sold in the U.S. since the mid-1970s has been equipped with a catalytic converter. Trucks began changing to converters in the 1980s to early 1990s. The best approach is to look for a "cat-back" kit, meaning, an exhaust kit that will have everything you need, starting at the rear of your existing catalytic converter. I do not condone removal of emission control devices! You can consider exhaust headers to replace factor exhaust manifolds, but due to their expense and installation difficulty, they need an article all to themselves.
So now you're wondering, how do I start looking? You should start by answering this question: what is most important: more power, better looks, or louder sound? If it's power, look for kits from companies that post actual horsepower and torque gains for your engine. Exhaust systems can absolutely improve your engine's power output, and they do so by maximizing the efficiency of the exhaust flow. Some specific techniques include converting from single to dual exhaust; utilizing straighter pipes with fewer bends; and making mufflers with fewer baffles and restrictions.
If appearance is the number one priority, then look for systems made of polished stainless steel. These look especially good on trucks- obviously the higher the lift the better because you will be able to see more of the shine. More appearance tweaks to consider are kits with optional side (behind the rear tires) exits vs. rear (under the bumper) exits, extended exhaust tips (the larger the diameter, the better!), and for certain pickups, vertical exhaust stacks that make your rig look like one of the big boys!
If a throatier sound is your goal, you are in a much better position than those of us who were purchasing performance exhaust twenty years ago. Most reputable aftermarket exhaust manufacturers utilize "sound files" on their websites. Enter your vehicle info, select a system you're interested in, and play the audio clip. Listen to 2 or 3 of them back to back, since what you're really chasing is the relative distinction among them. When you've heard one you like, you'll have a pretty good idea of how your ride will sound.
If you consider yourself a DIY pro and have tools available to get your car a couple feet in the air, installing a custom exhaust system is not very difficult. However, for older vehicles, you will likely be dealing with some rusty clamps and pipes, which will accelerate the frustration issue. If you have any doubts about your ability to get this done, a professional who has access to a lift and power tools can do it for you for relatively cheap.
Regardless of if you're driving a 20-year-old econobox, a late-model muscle car, or a pickup truck of any vintage, you can completely transform your ride by bolting up a cat-back vehicle-specific exhaust system and own the road. What transformation are you looking to make?