Disclaimer: I am the author, creator, owner and maintainer of Have I Been Pwned and the linked Pwned Passwords service.
Let me clarify all the points raised here:
The original purpose of HIBP was to enable people to discover where their email address had been exposed in data breaches. That remains the primary use case for the service today and there's almost 5B records in there to help people do that.
I added Pwned Passwords in August last year after NIST released a bunch of advice about how to strengthen authentication models. Part of that advice included the following:
When processing requests to establish and change memorized secrets, verifiers SHALL compare the prospective secrets against a list that contains values known to be commonly-used, expected, or compromised. For example, the list MAY include, but is not limited to: Passwords obtained from previous breach corpuses.
That's what Pwned Passwords addresses: NIST advised "what" you should do but didn't provide the passwords themselves. My service addresses the "how" part of it.
Now, practically, how much difference does it make? Is it really as you say in that it's just like a one in a million front door key situation? Well firstly, even if it was, the IRL example breaks down because there's no way some anonymous person on the other side of the world can try your front door key on millions of door in a rapid-fire, anonymous fashion. Secondly, the distribution of passwords is in no way linear; people choose the same crap ones over and over again and that puts those passwords at much higher risks than the ones we rarely see. And finally, credential stuffing is rampant and it's a really serious problem for organisations with online services. I continually hear from companies about the challenges they're having with attackers trying to login to people's accounts with legitimate credentials. Not only is that hard to stop, it may well make the company liable - this popped up just last week: "The FTC’s message is loud and clear: If customer data was put at risk by credential stuffing, then being the innocent corporate victim is no defence to an enforcement case" https://biglawbusiness.com/cybersecurity-enforcers-wake-up-to-unauthorized-computer-access-via-credential-stuffing/
Having seen a password in a data breach before is only one indicator of risk and it's one that each organisation using the data can decide how to handle. They might ask users to choose another one if it's been seen many times before (there's a count next to each one), flag the risk to them or even just silently mark the account. That's one defence along with MFA, anti-automation and other behavioural based heuristics. It's merely one part of the solution.
And incidentally, people can either use the (freely available) k-Anonymity model via API which goes a long way to protecting the identity of the source password or just download the entire set of hashes (also freely available) and process them locally. No licence terms, no requirement for attribution, just go and do good things with it :)