Came here to mention Shadowrun SNES. Terrific atmosphere, terrible combat mechanics. Didn't stop me from playing through it at least three times, though.
Shadowrun Returns doesn't capture the grittiness of the world nearly as well. There's just something about it that makes it feel... I don't know, bland? It's just missing a certain something.