Excel 2007 : vlookup not working in every cell

INDEX ( return_range , position_in_range )
MATCH ( lookup_value , lookup_array_or_range , type )

In an INDEX/MATCH function, the MATCH portion is returning the position_in_range to the INDEX function. It becomes:

INDEX ( return_range , MATCH ( lookup_value , lookup_array_or_range , type ) )

In your case, you want to return values from column BU (BU2:BU1000), so that becomes the "return_range".

In the MATCH function you would normally provide a lookup_value (or cell reference containing that value), a column to search and a match type (-1, 0 or 1).

Match type 0 looks for an exact match. Type 1 looks for an approximate match in a list that is sorted in ascending order. It will return the largest value less than or equal to the lookup value. Type -1 looks for an approximate match in a list that is sorted in descending order. It will return the smallest value greater than or equal to the lookup value. See Excel help for examples.

In this case, rather than searching a range of values for a specific value, I'm doing an approximate match and searching an array of values for 2. The array is created in the "1/(BT2:BT1000=A6)" part of the formula when you press CTRL+SHIFT+ENTER (array-entering a formula). This results in an array like "1/(TRUE;TRUE;FALSE;FALSE;FALSE)" which in turn becomes "1;1;DIV/0;DIV/0;DIV/0" because TRUE=1 and FALSE=0. 1 divided by 1 = 1 while 1 divided by 0 = DIV/0 error.

Since I'm trying to match 2 to an array of 1's and errors, the MATCH function will return the position of the last entry in the array that is less than or equal to 2 - in other words, the last instance of 1.

It's always tough to explain these things in words. If you select the cell and then Evaluate Formula (Formulas tab, Formula Audition section, Evaluate Formula) you will see each step as you walk through it.