Please don't list this on a work's page as a trope. Examples can go on the work's YMMV tab.
We're gonna play fetch. You are the frisbee.
A character who, despite or precisely because of being Nightmare Fuel is also greatly loved by a series' fandom. This can have a variety of reasons. Maybe the character is simply the biggest Badass in the show, or maybe s/he also has a cute, funny or tragic side in addition to his or her creepy side that endears the character to the fans. The character in question need not be a villain, though a majority of characters this trope applies to are villains or at least antagonists of some sort. This sometimes results in Misaimed Fandom and Draco in Leather Pants.
Note that while these characters are often Nightmare Fuel incarnate, their appeal to non-Nightmare Fetishists is often due to being more appealing (e.g., Magnificent Bastard, The Woobie, etc.) than creepy.
May or may not overlap with Creepy Good.
Sawyer the cleaner might also count. A young woman who needs a electrovoice after having her voice box cut out and works as one-woman Cleanup Crew, using her trusty chainsaw to make unwanted bodies easier to hide by cutting them into pieces ...bodies who aren't necessarily dead when they are brought to her. She also has worked as a hired gun a few times, proving quite adept at using her chainsaw in close combat.
She is just plain scary up to the point where she loses her voice-gadget, gets upset about it, and shifts from monster to moé in a matter of seconds.
For all his high talk, Ulquiorra wants to be appreciated as such (and maybe even as a Magnificent Bastard, with his Nietzsche WannabeBreaking Lecture attempts), transforming specifically to creep Ichigo out in a hopefully awesome way. He's actually surprised Ichigo doesn't buy it (while not buying a lot of other shit he spills).
And let's not forget Hollow Ichigo V2 "Ressurection". Devil horns? Devil look? Still awesome, even in previous versions.
And least during the Rescue Rukia/Soul Society arc, Kenpachi could certainly be classified as this. He kinda moved out of this category after becoming one of the good guys and instead was simply classified as Crazy Awesome instead.
Unohana of all people has become this after the release of Chapter 523.
In Battle Royale Kiriyama is usually seen this. Being an emotionless killer doesn't stop fans from liking him.
Hellsing's protagonist Alucard, due to being a serious Badass, among other things. Also, Jan Valentine.
There's also Crona, the woobie who has a bully living in their spine. The adorable woobie who has a bully living in their spine.
The D-Reaper from Digimon Tamers. The creepy part is self-explanatory, the awesome part is the fact that this enemy came the closest to its goals and it's likely the most powerful ...thing in the Digimon franchise.
For recent viewers, seeing noble Aragorn as a creepy blondLucifer in the first of The Prophecy movies has an added layer of creepy awesomeness. Virtually every moment he is on-screen will make your skin crawl and be among the most memorable portions of the movie. A deciding factor in why people love his performance is probably that he manages to be creepier thanChristopher Walken.
Mapleshade is so crazy that even Tigerstar, the Big Bad, is scared of her, goes on murdering sprees at the slightest provocation, looks like a demon in her official artwork, could be sneaking up on the characters to murder them at any time you read the books because she's invisible to normal characters and nearly-invisible even if you have the power to see her, and is implied to be a cannibal. The fans think she's awesome.
Sol can Mind Rape you by walking nearby, is always completely calm except for one nightmarish moment where he lost his temper, has an insatiable desire for vengeance, and is just creepy in general. These things have made him one of the most unique and well-respected antagonists in the series.
Live Action TV
Sherlock Holmes keeps a human head in the fridge, eyeballs in the microwave, and is forced to ride home on the Tube when cabs refuse to take him because he's covered in pig blood. Fans love him.
Firefly: River Tam, when she's not being a hysterical, crying Woobie that you just want to hug because of what the Alliance did to her brain, can be one of the most frightening people you can ever meet. And she is also awesome, especially in the Big Damn Movie when she Takes A Level In Badass.
This also applies to several of the good guys. For example, the Fourth Doctor's behavior is often very unsettling, but he's still the most iconic incarnation of the Doctor.
Snafu from The Pacific. He throws pebbles into a Japanese soldier's blown-open head, pries out their gold teeth and has major personal space issues with the other Marines, but fandom adores him because he's fascinating to watch and develops a very sweetly protective friendship with Eugene. Full-out acknowledged by actor Rami Malek in a radio interview:
Interviewer: You were creepy in that show, dude!
Rami:(good-naturedly) I think I'm creepy in everything.
Captain Ronald Speirs from The Pacific's predecessor series Band Of Brothers, due to him having gunned down several German POWs and shot one of his own men for disobedience. The example works in-universe as well, since men of Easy Company think him awesome yet terrifying.
Radiohead's "Climbing Up The Walls". A song written from the perspective of something living in the mind of a Serial Killer, sung in a slurred, eerily distorted voice over heavy guitars, dissonant strings and creepy, abrasive electronics. It's also a fan favorite.
Most of the Industrial genre, in particular the abrasive early acts like Throbbing Gristle and SPK.
Dark Ambient music, especially the subgenre known as Isolationism.
WWE: "The Boogeyman." He was originally supposed to be a heel, but he became so popular with the fans that WWE had no choice but to turn him face. Didn't stop him from chewing on earthworms and spitting them in his opponents' faces.
WWE: The Undertaker practically runs on creepy awesome, and, even if one says that that is no longer the case, it definitely was in the early to late 90's.
Kane perpetually sits here, whether he's a face or a heel, masked or unmasked. He's worked every creepy aspect imaginable with his character, and nearly every one of them he's worked to perfection.
Jake "The Snake" Roberts plops firmly into this in his heel role, what with the leering, lecherous stare, the fascination with serpents, the happy admittance that he's a lying snake, and the fact that his semi-disheveled appearance would put him just as equally in an alleyway wearing nothing but a trenchcoat and sneakers. Of course when he's a face, he's a different kind of awesome.
To give a specific example, consider the Obliterator virus. At first, it just heightens weapon proficiency. However, as it runs its course, the infected gains the ability to spontaneously generate ammo, absorb weapons into his body and recall them, and eventually even create new limbs and organs. Of course, this all comes at the cost of losing one's sanity and humanity.
BioShock as a whole. Sander Cohen. Dr. Steinman. The Splicers. Big Daddies.
.hack's Skeith. That One Boss, Name To Run Away From Really Fast, Grim Reaper motif, creepy theme music, fan favorite. Azure Kite is also more than a little creepy, being essentially a zombified version of the first game's protagonist that is practically invincible and attacks people with no apparent provocation. The fact that he tends to be a major badass about it probably helps.
Skeith was so popular in fact, that he was essentially made playable in the sequel. The same could be said for Haseo, who was the resident Psycho for Hire in SIGN with some Amnesiac Dissonance.
Ridley of Metroid. Even the one person who seems a legitimate threat to him, the greatest bounty hunter in the galaxy, who takes on entire planets full of parasitic horrors, is terrified of him. And he's a dragon, which automatically makes him cool.
Albedo, the crazy, regenerating, Evil Albino from Xenosaga. If you didn't get into the series because of KOS-MOS, chances are you got into it because of him.
Creepers, the horrible kamikaze shrubs from Minecraft, are quite popular with the fanbase precisely because they're paranoia-inducing abominations.
Irisu / "Alice", the heroine of Irisu Syndrome. Certainly doesn't come across as such at first glance (and given she's the protagonist, this is a rather big plot point): She appears to be a meek, quiet girl with a thing for bunnies and who likes cosplaying as a witch / maid. In reality, that's exactly right... except when she was very young someone killed her pet rabbit, leaving it's decapitated head for her to find topping the pile of gore, which broke something inside her, mentally and spiritually. She's now a drug addicted serial killer who thinks nothing of murdering a group of people so the boy she's stalking will have time to pursue his own psychosis. And, oh, how the fandom loves her for it.
Alice in Shin Megami Tensei is a small, innocent girl in a blue dress who will happily scamper around highly dangerous locales in search for a friend. She's actually an undeadHumanoid Abomination gifted with enough raw magical power to murder everything and everyone, and an astounding lack of understanding on exactly why it's not commonly appreciated to torture, kill and eat your friends. Her weapon of choice? The corrupt armies of Wonderland.
A fair number of the Daedric Lords from The Elder Scrolls V Skyrim, especially Boethiah and Hermaeus Mora. Boethiah sounds like a combination of a crotchety old lady and the general of the army of Hell itself, and has a perpetually-dark shrine high on the side of a mountain which is filled with anarcho-nihilist cultists, huge bonfires, burned corpses, ancient architecture, random blood splatters, and one badass statue. Mora appears (as a swirling void of purple and black, or a green void of eyes and tentacles if you have the Dragonborn DLC installed) to you in a hollowed-out iceberg where a lunatic's made his home, and names you his champion in an Affably Evil voice before explaining that he is the lord of all knowledge and discovery. And the artifact he gives you is widely considered to be one of the best in the game.
Cicero also qualifies. He's obsessed with his "Mother" (actually the Night Mother, the dead/undead? leader of the Dark Brotherhood), is not above casual murder as per his membership in the Dark Brotherhood, dresses, talks, and dances like an unhinged jester, and is a fan favorite specifically because of that.
Another rare heroic example in Colonel Jade Curtiss from Tales Of The Abyss. He's the Mad Doctor who invented fomicry, and, using this science, created the game's Bonus BossHumanoid Abomination. He also mutated his own body multiple times - including fusing a spear with his arm and altering his eyes so they increased his skill with fonic artes and turned red. He is a sociopath who killed small animals as a child. His own sister calls him a monster. But he's also, without question, the game's king of comedy, absolutely badass, rather pretty and elegant, extremely well voice-acted in bothlanguages, and overall an Ensemble Darkhorse to be reckoned with.
The Arishok from Dragon Age II. While the Qunari have been redesigned to look significantly less human than they did in Origins (never mind their philosophy, which treats mages like absolute dirt and is otherwise logical to a fault), he's still quite popular among the fandom. Several fans are even less-than-amused at the fact that you can't join him, though given the other two brands of crazy in charge of Kirkwall, it's not so surprising many want to Take a Third Option.
The Hydra from Warriors Orochi 3. Not only does it look really intimidating and impressive, but also for the fact that even before the game starts, it slaughters every one of the Dynasty, Samurai, and Original characters aside from a small handful. It is also practically invincible until the end game, and pretty much all attempts to take it down beforehand will be met with miserable failure.
It is hard to describe but this definitely applies to the Slender Man. Mind you, fans still treat him like pure terror but he is also quite popular precisely because of his uniquely subtle, yet terrifying creepiness. Why else would hundreds of people work actively to turn a creepy-pasta produced Memetic Mutation into a genuine Urban Legend?
Terezi Pyrope of Homestuck, at least initially. She is probably the single largest Nightmare Fuel Station Attendant in the comic; she was practically introduced by trying to kill John and succeeding in a Bad Future, though she admitted that she was well aware it wouldn't actually work out. Then came Act 5, and with it context and character development aplenty, where it was proved that as trolls go she is probably one of the most pleasant. Even before then, she is one of the most popular characters in the fandom, though that may be more related to her more positive development.
Star Wars: The Clone Wars recently had three episodes with Tarkin. We knew who he was and what he was going to become. We saw him bonding with Anakin, making sure we saw A New Hope in a new light. And we watched as he served under Evan Piel, one of the most badass Jedi in existence. It was definitely creepy and very awesome.
Transformers Prime brings us the ever-present, silent, faceless Soundwave. There's a reason everyone but Megatron fears him- he's capable of not only gathering intel from nearly anywhere on Earth, he's an expert at curb-stomping upstarts who disrespect Megatron. He's so cool, he's capable of snarking without even speaking. And when he does speak, he doesn't use his own voice, but instead prefers to Quote Mine the words of others and play them back.