Array Formulas can be a very powerful tool in Excel. Here is an introduction:
http://www.cpearson.com/excel/ArrayFormulas.aspx