I liked the exhaust note of the LS1 in my '04 GTO. It was a little on the loud side, but it had a nice tone.
I like the sound of the V12 in the Xj12 sucking all that air through the intakes.
I liked the turbo whine in my '83 T Bird.
I don't care for loud exhaust at all. After market exhaust systems don't have an appealing sound to me, personally.
edit: I also think Pontiac did a good job on the exhaust note on the Torrent GXP. For a V6, it has a nice tone and the right amplitude - not too loud and not too soft.