Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
vary the vlookup array depending on the value in a cell | Excel Worksheet Functions | |||
Using single cell reference as table array argument in Vlookup | Excel Worksheet Functions | |||
VLOOKUP in SUMPRODUCT array | Excel Worksheet Functions | |||
Array Function with VLOOKUP | Excel Worksheet Functions | |||
Match, Index, Vlookup, Large....Help Please | Excel Worksheet Functions |