One more vote for Gauntlet Legends!
It's my favorite co-op N64 game. By a mile. However, it is a sort of hybrid RPG / Beat-em-Up and requires a bit of a learning curve at the start. It's very simple, though. Once you get the hang of the inventory system and how items / pickups work, you'll be fine. It's an easy game to get sucked into for hours at a time.
If you want some mindless arcade fun, I would also suggest Rampage World Tour! Though a lot of the enjoyment of the game depends on how much you love the hokey Godzilla-style movies of the past. Each level is more or less the same - you play as giant monsters in a city, which you promptly proceed to tear to the ground. Simple, straightforward fun for two or more players.
The best thing about both games is that they don't use splitscreen! Even with two, three, or four players, all of the action takes place on the same screen. And in both cases, it works very well, with camera issues rarely getting in the way.
I'm also partial to the N64 version of Hexen that someone else mentioned, but being a fan of old-school Doom Clones is a requirement. Still, what a rare sort of co-op game it is, especially on consoles. If you like searching for keys and flipping switches in an endless series of corridors, it's the game for you
