Vlookup with col index no. array
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.
|