It really depends on what it is your looking for, but I'll list of a few.
Cube Experimental - This is a quest mod that adds a massive underground area to the game and basically turns Fallout into Half-Life/Portal. I had a great time with it.
Fallout: Wanderer's Edition - a compilation of a bunch of mods all designed to make Fallout more like a survival sim. The number of changes are far too numerous to list but some of the stuff includes more chems and a more realistic addiction model(taken from the Better Living Through Chemistry mod), revamps xp and enemy ai, revamped perks, required water/food/sleep, adjusted weight system, etc etc.
Mart's Mutant Mod - this mod adds new creatures(mostly stuff from FO1 and 2), new variants of existing creatures(armless goul, deathclaw matriarch) and new models of existing mobs in the game with different colors, etc. It also adds a variability script that randomly adjusts the size of every mob when it's spawned by the game and changes it's stats accordingly. The mod has options for wounding effects on mobs and also AI tweaks and hunting scripts.
Real Time Settler - This one is pretty damn amazing but also hugely complex and hard to learn. It lets you build your own town basically anywhere in the game. You can have up to 20 NPCs living in it and have to manage all kinds of shit like who goes hunting for food and who builds, etc. Basically everything in the game that can be picked up by the physics engine can be salvaged into special materials used to build your town(it doesn't use existing inventory materials unfortunately). The town has a tech tree of sorts and purchasable blueprints so it's pretty similar to most city building sims, just done from the 1st person. You can also "terraform" to a degree, picking up and moving cars(build walls with them!) and sending guys to root through piles of junk, which actually clears them off and keeps your town nice and neat. The mod also includes a controllable raider attack if you want to test your defenses.
FOOK2 - I think this was originally called Fallout Overhaul Kit or something, but everyone just calls it FOOK now. This mod adds dozens and dozens of new guns to the game, nearly all of them based off real life guns. Fair bit of retexturing of existing stuff too. Also adds nightvision goggles and a bunch of other stuff, I don't know what else is in it these days and it's just continued to grow over the last couple years.
Weapon Mod Kits - this one might actually be rolled into FWE listed above but it's still available by itself. This basically adds weapon tinkering to the game. Using a workbench you can do stuff like take the scope off a sniper rifle and put it on a hunting rifle, etc. Includes stuff like laser sights, extended magazines, silencers, etc. The mods themselves can also be found as loot. Has a patch that makes it work with the FOOK weapons too.
GNR More Where That Came From - Adds up to 100 new songs to Three Dogs's rotation, all from the style/era of the game's existing songs.
Fellout - Graphics only tweak that takes the green tint off everything and brightens up everything. Has an optional weather system to add raid and storms.
FMM and FOSE - Fallout Mod Manager and Fallout Script Extensions are two mods you're probably going to need to run basically any of these that have gameplay tweaks(Cube doesn't need it)
All the naked shit - I don't find these particularly interesting but since they are among the most popular mods for Fallout I should mention the Dimonized Body Type3 and Animated Prostitution mods. The Type3 mod is a female body replacer which I think basically adds a new model for every female in the game. And of course, unlike the Bethesda models, these are not textured with tank tops and panties. Most people want these to do shit like make Clover run around naked, but to Dimon's credit, they DO look better, if not particularly "I've survived the nuclear apocalypse". There are several body replacements but Dimon's Type3 is by far the most popular and therefore has the most followup mods from other people(clothing/armor packs, new companions, etc). Animated Prostitution really doesn't need a whole lot of explaining. Lets just say it makes Hot Coffe look like a joke.
There are really dozens(hundreds?) of others but the ones above are probably the biggest in their respective category. I get nearly all my mods from Fallout Nexus and they have a top 100 list so check it out.
