Left 4 dead 1&2, Halo 1-3 + Reach. Man I miss co op games with friends on split screen. Do modern games even have split screen options outside of Nintendo titles?
+1 for the Bungie Era Halo franchises, IMO they have one of the best stories, and they honestly all hold up well. Plus you have so much that you can burn through with friends, and it has such deep lore you'll be chatting for hours.
1, 2, 3, ODST, and Reach are all great for this. 1 and 2 support 2 players. 3, ODST, and Reach support 4 players
Portal 2 has a very fun co-op story mode. In fact, I'd recommend the solo story mode for both Portal 1 and 2 as well. One of my favorite video game stories ever.
I haven't played them myself but I believe both It Takes Two and A Way Out were designed to be played in co-op only so both their stories should be good.
Played it with my SO, reached a certain scene that completely warped the tone of the game (I'm guessing you know which one I mean), and neither of us really have had the urge to play since.
Also, both parents are kinda unlikable as characters imo, lol.
I've spent the last year or so playing Tom Clancy's Ghost Recon Breakpoint with a couple friends.
It has an interesting sci-fi/military story, tons of side missions if you're tired of following the main campaign, and a wide open map to explore if you're just bored of everything. Plus, there are random missions every day, so if you're done with the main campaign, you can continue to do missions and enjoy exploring the world even more.
Also, your party doesn't have to stick together. You can play on the same map, but go off and do your own thing. I have a buddy who can't follow instructions to save his life. He's always running around, causing chaos everywhere he goes. We're trying to stealthily infiltrate a base and he just crash-lands a helicopter into it and runs in guns blazing.
So... we let him run off and grief other bases or enemies while the rest of our party focuses on the mission. Everybody wins, and we all get to play together and have a good time.
EDIT: Same goes for Tom Clancy's The Division and The Division 2. Unlike Breakpoint, which takes place on an island nation, fighting against a wannabe dictator, The Division takes place in America after a virus plague has wiped out most of civilization, and you're playing as an elite team that's trying to restore order to the population.
I've been playing The Division with my friends for a few years now. It's a very fun game series.
Secrets of Grindea and Dysmantle. I'm sure I'll come up with a lot more, I go deep diving all the time for games for my partner and me
Secrets of Grindea - Tongue in cheek, Secret of Mana inspired ARPG. They recently finished the story, and there is also an arcade mode that is a blast with friends
Dysmantle - Survival Crafting Lite where everything is destructible. We REALLY enjoyed this one. I've downloaded it again for us to play through a second time. One of the DLCs is mediocre, but the other two were fantastic. I still enjoyed all three
Edit: I don't think I saw anyone mention Outward. A difficult, medieval action RPG
Edit 2: this may be too light on story, but Cat Quest 2 and probably 3 (I haven't played it yet). Cozy ARPG Lite. We liked it a lot