The best, most expensive amps still use valves, but if you're like most people you're not going to want to spend upwards of 20,000 on a stereo power amp just to get that valve sound. So for studio use you're more likely to find valves hiding in a box that pretends to do something else, such as a compressor, an outboard EQ unit or even a mixer. This is where you run into retro syndrome. As with analogue synths, some valve kit is old, classic and completely beyond the budget of mere mortals. Some of it is old and crap.
Some newer kit has that magic.
And some doesn't. Let's start with the old and classic kit first. If you're lucky enough to land yourself recording in a studio that has this kind of kit, make sure you take the time to make use of it. Names to watch for include Pultec and Fairchild. Some engineers swear by these things and use them on everything.