MrExcel Publishing
Your One Stop for Excel Tips & Solutions


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

Column where to look

6 columns to the left of this one

Formula would be in Sheet2.




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...


The array formula...


...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