VLOOKUP AND AVERAGE ?
Posted by Dave on January 28, 2002 10:57 AM
I would like to do an exact vlookup and when found I would like to average 6 columns, Instead of just returning 1 column.Is this possible to do using the vlookup formula? If not can anyone help me out with another formula ? My sheet needs to go to an exported worksheet find a match and average columns in the same row....
Posted by Juan Pablo G. on January 28, 2002 11:11 AM
How about this. Assuming following.
Looked data in
=Sheet2!A1
Column where to look
=Sheet1!A:A
6 columns to the left of this one
=Sheet1!B:G
Formula would be in Sheet2.
=AVERAGE(INDIRECT("B" & MATCH(A1, Sheet1!$A:$A, 0) & ":G" & MATCH(A1, Sheet1!$A:$A, 0)))
or
=IF(COUNTIF(Sheet1!$A:$A, A1), AVERAGE(INDIRECT("B" & MATCH(A1, Sheet1!$A:$A, 0) & ":G" & MATCH(A1, Sheet1!$A:$A, 0))), 0)
Juan Pablo G.
Posted by Adam S. on January 28, 2002 11:12 AM
I can think of 2 ways...but neither are as efficient as I'd like.
I don't know the exact layout of your data but I'll assume your lookup range as A2:G2000 for example purposes, where you want to average B thru G columns when you a value to A.
1> Insert a column (probably normally hidden from view) with the formula: =ROW(A2) ->assuming A2 starts your lookup range. Based on this example say this is tossed into column H.
Instead of your Vlookup alone use: =Average(Indirect("B" & Vlookup(input,$A$2:$H$2000, 8 ,false) & ":G" & Vlookup(input, $A$2:$H$2000, 8, false)))
2>an average of 6 vlookups:
=(Vlookup(,,2,false) + Vlookup(,,3,false)..... + vlookup(,,7,false)) / 6
Hope that helps
Adam S.
Posted by Mark W. on January 28, 2002 12:36 PM
Too easy...
Suppose your lookup table in cells A1:G6 is...
{1,4,15,12,25,22,11
;2,24,6,18,16,19,16
;3,7,8,11,8,21,21
;4,13,4,15,9,22,12
;6,7,23,22,10,15,24}
The array formula...
{=AVERAGE(VLOOKUP(2,$A$1:$G$5,{2,3,4,5,6,7},0))}
...will average the values in columns B:G on row 2
and return 16.5.
Note: Array formulas must be entered using the
Control+Shift+Enter key combination. The
outermost braces, {}, are not entered by you --
they're supplied by Excel in recognition of a
properly entered array formula.
Posted by Juan Pablo G. on January 28, 2002 12:39 PM
Re: Too easy...
Got me there. Nice formula
