Hi

I generally recommend that you don't use whole columns for any formula that processes arrays, because the arrays will be oversized and slow down the calculation. That said I'm not actually sure if that applies in this context, or if XL07 handles this better... So no

__real__ problem if you are using XL07, just a recommendation made on my part more as a precaution.

With respect to your evaluation of the formula, you're close but not exactly spot on. The lookup value will always be 2. If LOOKUP can't find the lookup_value, it uses the largest value in the array that is less than or equal to lookup_value.

The array we use,

__1/((ColA=1)*(ColB="a"))__, will initially evaluate each condition statement first (e.g. ColA=1) and return an array of boolean results based on wether or not the condition is met for each cell in the range (returns e.g: {TRUE,FALSE,FALSE,FALSE...}. The two arrays are then mutlipled against each other, which also acts as coersion (multiple a boolean by another will yield 1 if both statements are TRUE, else 0). This then forms a single array {1,0,0,0,0,0...}. Each value in the array is divided by 1, which then yields an array of only 1's and #DIV/0!'s, e.g. {1,#DIV/0!,#DIV/0!,#DIV/0!,#DIV/0!...}.

Lookup(2,TheNewArrayDescribedAbove,TheRangeToReturnValueFrom) will return the result from the

__last__ 1 found (in my example the only 1 and also happens to be the first array item), and then return the value from the range in equivalent position.

I'm not so hot on explaining these things but you can search for this method posted by Aladin and Jonmo1. I know both have gone to some length explaining this method.