We are at a point where exploration does not necessarily have to have a direct, clear, objective, obvious cost-benefit analysis in order for it to be a worthwhile endeavor.
Apollo also generated little science that couldn’t be done by machine. We have returned samples from comets, doing so from the Moon isn’t that hard comparatively aside from delta-v requirements.
The Space Shuttle? Massive waste of money just to put some satellites and telescopes up in orbit. A massively overcomplicated, and dangerous vehicle system that cost way more in the long run than the “disposable” rockets it was meant to replace.
None of it matters. The frontier on Earth is all but gone. There is no line to tow, no limit to push against and hang your ass over the ragged edge.
But doing so forces us to figure out solutions to problems we would’ve never faced otherwise, solutions that often have incredible utility back home. Artificial limbs. Insulin pumps. Camera phones. Aerogel and memory foam. Photovoltaics. CAT Scans. The list goes on. Innovations that either started or aided by the problem of making it lightweight/storable/safe for space flight, both manned and unmanned.
The list of problems that will need to be solved in materials science, computer science, rocketry, mechanical engineering, etc etc, of putting humans on the Moon for long term stays isn’t even written yet. It is the only worthy effort of our society to pursue it, after and during ensuring everyone is fed, housed, and educated here. Otherwise, what the hell are we doing here? Toiling away, making the funny numbers in the computer go up?
The universe has made it clear that if you aren’t busy growing, you’re busy dying.