I’ll give my take on the Comp Sci example. If a school is known for having a strong engineering program overall, all the major tech companies will recruit there even if the CS department isn’t ranked as highly as its other programs. So if you want a job straight out of undergrad, the school might be more important. If you want to go to grad school, having a strong CS program might be more important. If you want to pursue a Phd, you really want somewhere that allows you to perform undergraduate research under a professor well known in their field.