I do agree that the game's story/atmosphere should be designed for singleplayer first, since that's what drew a majority of people to the first game.
However, I think MP could still retain some sense of atmosphere as long as they have a good in game voice chat and keep it proximity based, like in Lethal Company.
It isn't scary at all for your teammate to tell you exactly where they just died, how, call out the leviathan heading your way, then continue with a joke they were making. But having a friendly conversation over the radio cut off by their violent death to horrors unknown could be scary, even if facing the unknown alone is fundamentally scarier.
I'd say Minecraft's multiplayer experience is close to what Anon describes as "good multiplayer", probably because it hasn't changed much in 15 years - there's not even an in game server browser (at least on the Java edition), and playing Minecraft in and of itself is usually a big time commitment so you're more encouraged to find a couple of servers you like and stick to them.
However, the last time that I feel like I integrated into a server's community was 4 years ago - a blank server list doesn't really encourage you to go looking for more, and it's been harder to commit time as I get older and have more responsibilities (that I ignore anyways, but still).
I think Lethal Company also has a lobby system without matchmaking, but I haven't played it so I don't really know.