I think one very good source for the kind of information you’re interested in would be Colleges That Change Lives
I was lucky enough to attend one of the “colleges that change lives” and I think that the undergraduate experience, where you choose to go, really can make a difference in some very important ways.
But as others have pointed out, there a number of factors you have to consider in choosing a college for yourself, which college is best for you, which will provide you with the best education and foundation possible. You need to consider what you want to study and if the college has a good department in that field, what your own needs are, would you function well at a very large state university with tens of thousands of students or would you do better at a private college or one of the smaller colleges in the public university system. And what do you want from college? For many people the college they attend, as you mentioned, may not make much of a difference. They will get out of it what they put into it and move on to graduate degrees in their field, and yes, a lot of time that is is more important that what college you attended. You can shine at almost any college and graduate schools will notice that. And, of course, all these choices sometimes come down to what you can afford.
And even if you don’t go on to graduate school, there really are a lot of very good schools in the U.S. and if you are motivated and put the effort into getting the most out of the college you end up choosing, you can get a good education almost anywhere.