It’s very effective, the forehead is the most armored part of your body when it comes to bone density. The same is not true for the rest of the face or the back of the head (which is fairly soft), but the forehead is very durable. The trick is not to smash your forehead into someone else’s the way they do in the movies or to wrench your head back to make it a big motion and then slam their forehead into the other guy’s like a pair of goats going at it.
When you headbutt, you actually want to aim for the softer parts of the face beneath the forehead like the nose where it’s going to hurt a lot less when you connect. That’s why you don’t just go forward, but also down. The softer the portions of the body that you connect with then the less damage you take from the force rebounding back into you. It hurts them a lot either way.
You also don’t have to leave your opponent’s head loose when you try to
headbutt. You have two hands, grab their head by the sides or by their
hair and wrench it back for better access to the sensitive parts if
you’ve got the option. This reduces the risk of you getting hurt by them blocking with their forehead.
Clanking two foreheads against each other is going to hurt a lot. Again, you’d be running face first into the body’s natural armor. If you have nothing else (like when your hands are locked up) then using your forehead to defend against your opponent’s forehead will actually work as a defensive action. It’s going to hurt, but it’s better than a broken nose. It’s also going to hurt them, which is a good thing.
This is also why you don’t want a big motion ever. Big motions like we see in in movies or martial arts performance are all about being eye catching, it’s about looking good/impressive and being noticeable from a distance. Movies especially are about how entertaining it is on a general level, rather than accuracy. Actual combat is comprised of very tight, very confined actions because you generate more power, waste less energy, and it’s more difficult for your opponent to react to because they’re harder to see the movements beginning.
Wrench your head back in a big way then by the time you’re ready to connect, your opponent will have lowered their own head to protect their face (or done something else) to counter that action.
The goal is always to hide what you’re doing to a certain extent so that by the time they recognize what’s happening it’s too late. If they see and you fail to connect, then they can create an opening and counter. In a normal fight between two people, that’s usually where the tension is. Every action you take is pulling from a rapidly depleting resource. Both participants need to connect, but there’s always the chance they won’t or they’ll miss or their attack will be turned against them. They’re looking reduce that risk as much as possible, and there’s no such thing as a sure bet.
Like anything else, the headbutt is effective if it connects. If it doesn’t, or if it doesn’t hit somewhere beneficial, then you pay for it.
That’s really the basic theory of all combat though.