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.

It is a very common technique to get an array of results which can be passed
to another (array handling) function. M any examples have been posted here,
for example

To add 1 month, but cater for that month having less days and not
over-spilling,

=MIN(DATE(YEAR(A1),MONTH(A1)+{2,1},DAY(A1)*{0,1}))

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Edward" wrote in message
oups.com...
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.