when done correctly, make-up can do wonders.
If you seriously don't believe me, look at any movie - all the actors are made up - they don't actually look like that, men and women. Every famous woman who's famous for her face, owes that fame largely to make-up.
I'm not saying that a natural look is ugly - I happen to love my girlfriend's natural face (you'd have to, otherwise you'd hate yourself every day), but a simple bit of make-up can accentuate strong areas, and draw attention away from weaker areas.
You're right about one thing though - you only notice it when it's done wrong.. that's sort of the point. The point is to make a woman (or man), look like they naturally look the way you want them to be.. If you fail in that illusion, then people will see the make-up. If you succeed, then the person will look like a natural person, only better.