Alright, I'll take a stab at it. Haven't played all of them, and haven't played any in a year(ish?), but let's see what I can do.
Fall Further (FF)
* Takes vanilla FFH2 and adds a few more civs and things. Not many new mechanics, and makes an attempt to fit the new civs in with existing lore, though not always successful.
* I only played a bit of this one, early on. It's pretty decent if what you're wanting is just "More FFH2", and isn't a bad place to start, either. Nothing really outstanding about it, in my memory.
Rise From Erebus (RiFE)
* A Modmodmod of FF, it adds even more new civs and mechanics. Attempts to be modular, but the launcher is somewhat buggy and the game often has issues if you add or remove modules.
* There's a lot going on in this mod, and it can take a while to wrap your head around all the new mechanics. Plays somewhat differently from 'vanilla' FFH2. One of the major new mechanics, off the top of my head, is that forts exert cultural control, slowly growing into improvements much like Civ5's citadels, and always have a free "Fort Commander" that slowly levels up and gets more powerful as time goes on.
* Development slowly dwindled, and now has a further fork, called...
Ashes of Erebus (AoE)
* A modmodmodmod, a fork of RiFE that's still (last I checked, anyway) receiving updates. Quite a few bugfixes from RiFE, and attempts to make the whole thing more stable and with fewer missing Civilopedia entries. Makes some changes to certain civs that not everyone agrees with, but is modular, like RiFE, so you can revert those changes if you want.
* Overall, RiFE and AoE have gone in an entirely different direction than 'vanilla' FFH2 and many of its modmods, and the lore especially can only really be considered to be 'inspired by' at this point. IMO it ends up feeling chaotic and watered-down, whereas FFH2 has some really tight and impressive lore, but if lore isn't as much your thing you could have a lot of fun with this one.
Orbis
* No longer an FF modmodmodmodmodmod but incorporates some of the civs from FF, IIRC. Brings back some CIV:BTS mechanics like corporations and espionage that were removed from FFH2 (given it first emerged before BTS did), and adds some of its own civilizations as well. Pretty decent mod, all things considered. Feels much more like a BTS game than an FFH game, in my memory.
Wildmana
* Never played it, outdated, completely replaced by...
Master of Mana (MoM)
* Still updated. Recently released (a few months ago) a massive update, version 4.0 or something.
* Drastic departure from Core FFH2 gameplay. Can be considered as much a total conversion of FFH2 as FFH2 is of CIV. Spell research is separate from tech research, many more resources to concern yourself with (wood, leather, metal, etc.), simplified mana system (only 4? IIRC kinds of mana, and they function entirely differently than in FFH2).
* With the most recent update and the drastic change to the mana system, can no longer really be said to be lore-friendly at all. Safest to say it's 'inspired by' FFH, I guess.
* Not my cup of tea, but worth taking a look at.
More Naval AI (MNAI)
* A modmod that doesn't add or remove anything from base FFH2, just DLL fixes and AI improvements. I think. Haven't actually played this one, but quite a few modmodmodmodmodmodmods incorporate the changes it makes. Think of it as almost fulfilling a similar role to Skyrim's Unofficial Patches.
Magister Modmod
* Aims to stay as true as possible to FFH2 lore, including adding or removing units and features with that in mind. Has many new features, and has one of the more complete civilopedias, and is still supported and updated, generally soon after any new MNAI patch.
* Makes no attempt to be balanced, but is tons of fun. My personal favorite of the batch, and that bias probably shows.
Erebus in the Balance (EitB)
* Uses MNAI as a base, seeks to add, remove, or tweak the game such as to make the civs relatively balanced, and therefore usable in competitive multiplayer. Haven't played this one myself, but I intend to check it out at some point.
* Its commitment is firstly to balance and secondly to vanilla FFH2, and therefore has the fewest changes of any of the modmods save MNAI. None of the new features of RiFE/AoE/Orbis, none of the overhauls of MoM, none of the changes of Magister's. If you're just looking for a fairly simple place to start diving into the world of FFH2 modding, this probably isn't a bad place to start.
I think that covers all the big modmods. Keep in mind that there are a billion modmods that just change or tweak vanilla FFH2, adding a new civ here or new unit there or that sort of thing. The ones I listed here are the big ones, the ones where you choose one and that shapes any other modding you'd do further.