As you’ve stated, it does greatly vary based on what work you’d be doing.
Being a SysAdmin, I’ve had my eye on Windows-Domain-VM-etc certs. Unfortunately, the MCITP series is set to expire soon. This is a problem when you consider that the MCSE/MCSA series slated to replace it is designed for the Server 2012 environment, an environment that hasn’t been adopted by many organizations yet. So, pursuing the MCSA/MCSE series would be more of a gamble than usual in regards to acquiring certifications. I mean, what is the sense in acquiring the certification if the Server 2012 OS doesn’t take off that well, a possibility that is quite likely considering the majority opinion of the radical design change present in the latest release of Windows.
Of course, exploring certs that are a bit unusual for your preferred job role can help you land a job, as you would be able to bring more to the table than other prospects who adhere solely to the certs that apply to the job in question. The difficult thing is in making these oddball certs applicable (or of at least some value to your potential employer).
For example, since it was announced a few years ago, I have been wanting to pursue the CompTIA Strata (I think it’s called the Green IT cert or something like that now). My plan was to use that as a means to put myself slightly above my competition for a job. If I could say that I have the same or similar certs as the other job-hunters but that I also have training in how to reduce the amount of electricity used by a company (thus saving it money), then I am able to bring more to the table for my potential employer. If I had documented proof of using my techniques to achieve a cost-savings at a previous employer, all the better.
Currently, there is a bit push in the medical field here in the States to move to electronic medical records (EMR). In fact, I am currently employed by a medical group in the SE. CompTIA has an EMR-specific cert that I’m sure would put an applicant in a huge lead for a job with my company, even if the job is for a simple break-fix tech.
In conclusion, I know that many people in IT view certs as a stopgap fix for lack of experience, but I think of them more as a tool to summarize ones abilities, as well as a means to show an ability to be multifaceted. With companies looking to get the most bang/buck from human investments (that is, employees), the ability to say that you can do what is required for the position plus more is always a good way to go.
EDIT: That having been said, it is possible to become over-qualified for a position. So, what you put on your resume and what you mention in an interview must be chosen carefully. You don’t want to outsell yourself or pile so much information on your employer that what you really want to communicate becomes diluted.