I was screwing around at work today and stumbled upon an interesting
way to manipulate v/hlookup-returned values.

For example, an average of two columns - array entered:

{=AVERAGE(VLOOKUP($A12,'[xxx file.xls]Sheet1'!$B$4:$F$10,{4,5},FALSE))}

Anyway, I was kind of tickled when it worked.

Incidentally,

=SUMPRODUCT(VLOOKUP($A12,'[xxx
file.xls]Sheet1'!$B$4:$F$10,{4,5},FALSE))/2

{=SUM(VLOOKUP($A12,'[xxx file.xls]Sheet1'!$B$4:$F$10,{4,5},FALSE))/2}

achieve the same result.

I would be interested to know how people have extended this idea, that
is, using an array of indices in similar formulas.