As far as the SNES is concerned, you definitely need to pick up Secret of Mana! Not only is it an awesome action RPG, but it is 3 player simultaneous! You do need to locate the other 2 party members for the multiplayer to activate, but that happens fairly early in the game. You could also look into Secret of Mana 2. It only came out in Japan, but there are English-patched versions that you could either emulate or buy on a repro cart if you want to play it in your system. I believe that SOM 2 is far superior, but SOM 1 is easier to acquire and cheaper if you're going cart-only.

I'm not sure of any traditional multiplayer RPG's on the NES, but for something a little unconventional, you should look into River City Ransom! An awesome beat-em-up with the ability to upgrade your stats and learn new moves! The most fun you can have with a friend on the NES!

Someone can correct me on this, but aren't the SNES Final Fantasy games multiplayer in the battle scenes?