How do I create a two variable lookup in Excel Spreadsheet?

One way to create a two variable lookup formula in Excel 2007 is to use the INDEX and MATCH functions.

First, the INDEX function returns either the value or the reference to a value from a table or range. The syntax for the INDEX function is:

Index( array, row_number, column_number ) where array is a range of cells or table, row_number is the row number in the array to use to return the value, and column_number is the column number in the array to use to return the value.

The MATCH function searches for a value in an array and returns the relative position of that item. The syntax for the Match function is:

Match( value, array, match_type ) where value is the value to search for in the array, array is a range of cells that contains the value that you are searching for, and match_type is optional.

Putting it all together:

=INDEX(LookUp!$AA$2:$AA$156,MATCH($C2&$D2,LookUp!$X$2:$X$156&LookUp!$Y$2:$Y$156,0))

An important note is you MUST enter it via ctrl+shift+enter, not just enter. You'll know you did it correctly when it creates an extra set of special brackets around your formula ({}).

An array formula can perform multiple calculations and then return either a single result or multiple results. Array formulas act on two or more sets of values known as array arguments. Each array argument must have the same number of rows and columns. You create array formulas in the same way that you create other formulas, except you press CTRL+SHIFT+ENTER to enter the formula.