Your goals should be weighted against the amount of debt you're willing to accrue. The biggest advantage of the bigger name schools is the networking opportunities. If you plan to branch out beyond the area in which your school is located, the bigger name schools definitely help.
In the grand scheme of themes, Yale/Emery/Hopkins are actually not the most expensive schools you could attend (any of the ones in Boston, New York, or DC, for instance, are all more expensive). Generally, the location of the school dictates price more than the school itself (because the school itself has higher operating costs).
When it comes to jobs vs. debt, consider if a bigger school, which affords you more networking opportunity, may land you a job that's more ideal for your interests versus having a lower loan re-payment but at a less ideal job. I'm not in any way suggesting you can't get your ideal job going to a lesser known school, but it's one aspect to consider.
Overall, though, if you don't have super ambitious career goals, I'd agree with your professor's advice and take a long hard look at the cheaper options (public in-state, usually). It'll save you a lot of grief in the long-run, particularly if you're flexible with what type of job you'd like in the future. And when someone says "won't get paid a lot", I think mostly everyone falls into that category if you're not a savvy business man or corporate lawyer (but maybe that's just me).
A PhD (or any doctorate) should only be pursued if that fulfills your objectives. A PhD isn't for everyone, the time commitment is at least double that of a MPH, and will only qualify you for a very specific subset of jobs (ie. research-based jobs).