Vlookup with col index no. array
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. |
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. |
All times are GMT +1. The time now is 08:36 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com