=vlookup(company name cell,the whole array on the other sheet that you want to choose from, the column number of this array that you want to look in,'false')
...I think. Excel help explains it pretty well
then use it in an if statement
=IF(vlookup(company name cell,the whole array on the other sheet that you want to choose from, the column number of this array that you want to look in,'false')>0,vlookup(company name cell,the whole array on the other sheet that you want to choose from, the column number of this array that you want to look in,'false'),'insert blank message here')
Sorry couple of edits there, i think that might work though, you might need to tweak it a little