It depends upon the man. My current male squeeze has a less than average-sized cock, but he's sexy has Hell. There's something about him that actually turns me on and it's not the size of his dick. However, I can say that in the plus column of things I like about his smaller penis is: it comfortably fits completely in my mouth so giving him a blowjob doesn't require I dislocate my jaw; he's uncut and easy to arouse; he understands that I don't expect him to stretch open his "back door" and suffer any pain, although I've met guys who thoroughly enjoyed taking as much of me inside them as they could; he knows how to get me off without having to be a gymnast; I like the feel of his semi-hard cock lined up between the cheeks of my ass when we spoon at night.
Sex with men who have large endowments has been a mixed bag of enjoyment and disappointment. It's nice to have company in bed with whom I can commiserate about the problems of shooting pain when someone bends your erection the wrong way or why you're not interested in being "bottomed." There are other ways of getting off besides just anal or vaginal sex -- fortunately. Sometimes the idea of a substantially or equally hung guy can be a turn on, but only if there is that sexual connection between us.
For me, sex with a guy who has a smaller dick presents a lot less potential problems. But that's me. We're all wired differently.
Good luck with that.