Ok, so from what I understand, it can be done, but I would have to provide my own certificate, and that is currently the only limitation?
Yup! There are some other finicky bits in the configuration, but I think nothing that would block you:
- no use of branch subdomains will be possible on that site
- custom domain has to match the pattern something.yourdomain.com (where you want us to serve *.yourdomain.com) - rather than being “yourdomain.com” or “someunrelateddomain.com” or “sub.sub.yourdomain.com”.
- no other hostnames (something.otherdomain.com) can be applied to that site.
What is holding Netlify back from using Let’s Encrypt to issue the certificate, if you don’t have control over the DNS? Could I not create a TXT record to prove ownership of the domain so that Netlify can then issue the certificates?
You could create that text record for yourself, and use it, yes. That’s basically what certbot does (and how our automation works too). We have not implemented any feature that would let you control it yourself using our automation + your DNS hosting to get the certificate, though, and it is not on the roadmap to add it. We control DNS, we get wildcard for you; we don’t, we don’t. They cost about $50 if you buy one for a year, so that’s your path forward if you don’t want to manage it more frequently 
Or, you could handle it using the free lets encrypt certs every 3 months; you could almost certainly reverse engineer the API calls we use to do it for your own use: [Support Guide] Understanding and using Netlify's API but I do not know anyone who has tried since you’d need programmatic access to rather sensitive data (your private key).
Will I still be able to have a separate site on Netlify running on our www.helloclub.com domain, while running the wildcard setup on our app.helloclub.com domain?
yup! individually configured hostnames on other sites override the wildcard which is more like a “catch everything else” than a “catch all”.
Finally,
I assume it’s no problem to switch the relevant sites over to that team once that’s setup.
Indeed, if you own the sites on the free team and are an owner on the new team, here’s how you’d move them between teams: Team-owned sites | Netlify Docs