Hey there, Janine! I hear you, it's incredibly perplexing when people seem to like you, yet you can't make any real social progress. Honestly I'm in the same boat, everyone seems to like and appreciate me in a general sense, but I can't seem to make an actual friend or a real connection to save my soul. Admittesly I don't actually try very much (a bit of a social hermit if I'm being honest) but I have tried, and met with about the same amount of success.
If I may ask a personal question, are you perhaps neurodivergent in any way? I'm an autist with adhd myself, and I've come to believe that's primarily what is behind my lack of social success. Neurotypical people tend to be somewhat uncomfortable when socially exposed to a divergent person for any real length of time. People like me don't quite understand the rules, you see. We do things in strange ways sometimes, ask odd questions or make weird observations. We can share a little too much, or expect others to share as readily as we do and create discomfort and pressure that we didn't even intend. I've become aware of myself doing that very thing on quite a few occasions. Hell, look at some of my posts, it's clear as day.
It doesn't mean there's anything wrong with me, I just work differently. And the people who avoid me aren't bad people, they're not, and they don't dislike me either. But there's a difference between liking someone and wanting to spend a lot of time with them.
I wish I could say I was immune to it myself, but I'm not. I have a coworker at my place of employment who I suspect to be an autist as well, but in a somewhat different manner than myself that I can't really relate to. Not all autists experience things the same way as each other. And he's a good guy! He's helpful, generally supportive, does good work. I like him as a coworker and an associate, and as a human person, I wish him only the best. But it still makes me uncomfortable to interact with him for any length of time, something about the way he interacts socially just creates this off feeling in my brain that I can't readily describe. So if he ever asked to hang out with me or wanted to have some kind of relationship beyond being coworkers, I'd probably have to say I'm good and back away. And the fact that I, someone who has experienced this reaction first-hand multiple times, can still experience it myself, just serves to emphasize that there's something going on either neurologically or within our social dynamic that is simply outside of our control.
I don't know that this is the case with you. I don't know your experience or your history, so it is entirely possible that none of this applies. But based on how you describe it, I struggle to think of any other explanation for why people would seem to be friendly under normal conditions but not really want to associate personally. And if it is true, it doesn't mean that they don't like you or are only pretending. It just means that you don't mesh well in your social interactions, and you might need to find someone who's a little more like you and can relate to you on your level.