MrExcel Publishing
Your One Stop for Excel Tips & Solutions

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