E
Edward
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.
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.