There is a distinction to be made between transactions (ie, transactions recorded on-chain) and payments (ie, a transferring of value between parties). Bitcoin can scale both by increasing the number of transactions per time, but also by increasing the number of payments per transaction.
The lightning network is a very interesting way of increasing the number of payments per transaction, the limit for number of payments is almost unbounded (there are channel liquidity considerations).
In any case, as a user what you should really care about is that you have some guarantee of ownership of your funds. You want control: to be able to spend those funds at your discretion, without any absolute dependance on or trust in a third party. Whether you get these guarantees through ownership of on-chain BTC, lightning-network BTC, or some other higher-layer solution is somewhat irrelevant (though there are additional security considerations in using higher layer networks like lightning. This is unavoidable: you incur all of the considerations of the base layer, plus the considerations of the higher layer). There is no reason that users cannot be on-boarded straight to lightning network, without any need to ever move their funds off lightning. The infrastructure may not be there quite yet, but development is ongoing at a fast pace, I think it will be very interesting to watch how lightning grows over the next decade.
Additionally, I think it is important to understand that even if bitcoin cannot scale to allow every person on earth to make on-chain transactions each day (or month, or year), that does not mean it is a failure or not useful. It just limits the usefulness of on-chain transactions to be higher-value settlements, and the limit of scaling then starts to depend on how many payments per transaction we can build infrastructure to allow.