Well, here's a quick list ("slightly" biased too):
*) Mutants Rising 
We're building a whole new adventure in the Fallout universe, related somewhat to Fallout 2 (character-wise). We haven't finished yet, but we have a
demo that you're welcome to check out.
*) Ardent's Mod -> My solo work. Not finished either, but the demo offers a couple of good hours of gameplay, and there are forking questlines etc. Check out my sig for links.
*) Shattered Destiny -> A little short, but well-crafted and interesting mod by Lexx. Also, this is a complete story with beginning and end, so it feels more satisfying to complete it, than other demos. To download it, check out the Files section here on NMA, or locate any post by Lexx and look for links in his signature. Lexx is very active, so it shouldn't be a problem.
*) Fallout of Nevada -> This is a quasi-finished total conversion mod, but you won't be able to play without a decent knowledge of Russian. There has been a "Google special" "translation" made, but it is of little help, if you want to get deeper in the story. Not really sure where to find the latest version, but googling it should help you.
*) Last Hope Mod -> This is apparently a completed total conversion mod by Forgotten Knight. It has some nasty bugs that prevent you from finishing it, and I'm not sure they have been fully addressed in the latest version, which would be, if I'm not mistaken, 1.07. It's still very much worth checking out, because Forgotten Knight did a good job on the mod, despite the bugs. Link:
http://www.nma-fallout.com/forum/viewtopic.php?t=33178&highlight=last+hope+mod
*) Fallout 1.5: Resurrection -> A Czech total conversion mod. According to the authors, it is nearly completed and should be released some time this year. I haven't had a chance to check it out, but so far reviews from playtesters have been very promising. Link:
http://www.nma-fallout.com/forum/viewtopic.php?t=60269
*) Fallout: Between Good & Evil -> Another chiefly Czech mod. No playable content is available so far, but Lizard is adamant it will get done sooner or later, and there are plans for a soon-ish release of the first chapter of the game. Link:
http://www.nma-fallout.com/forum/viewtopic.php?t=33726&start=240
And last, but not least:
*) Olympus 2207 -> This is not your usual Fallout 2 mod, because it features a completely new setting, only using Fallout 2 engine. All graphics have been redone for this game, so it's worth checking out, even if it's just for the art (but the story and scripting is just as good). Link:
http://www.nma-fallout.com/forum/viewtopic.php?t=61557
