In particular, any mods that actually create new areas, plot/stories, etc. It doesn't have to be related to Fallout: New Vegas or the Fallout series. I don't usually see that sort of mod discussed, but I've seen a few awesome what-if plots, levels, etc. that past mods have made in other games, so I'm interested in whether there's a notable one for F:NV.

That's a totally subjective thing, and there are so many available that no one is going to have tried them all out. So your best bet is just to have a look at what's out there and try some out. If you go to the Nexus, you'll see a special category for new areas/quests etc., which will give you a place to start looking. As far as areas are concerned though, you might find this one interesting:

http://www.nexusmods.com/newvegas/mods/43534/?

CevSteel has an amazing gift for creating new interiors for these games, adding spaces in buildings that are normally boarded up and inaccessible. He did some really great stuff with Fallout 3 as well. This one is a joint project with bigcrazewolf, who has a definite knack for creating interesting interiors as well.