PHYSICAL CONDITIONS OF THE MULTI-PHASE INTERSTELLAR MEDIUM IN NEARBY GALAXIES FROM INFRARED AND MILLIMETER-WAVE SPECTROSCOPY