excel - VLOOKUP average a range of cells -

i have standard vlookup formula

=vlookup($g28,'analysis 1'!$a$2:$cr$32,$m28+$m28,true) 

how amend rather return answer want average result of 3 cells, cell above vlookup, cell below vlookup , cell of vlookup?

i if can specify number of cells either side of vlookup want average, in case above 1.

yes need use match.

i think easiest way using offset function:-

=average(offset(b1,match(e2,a2:a11,0)-d2,0,d2*2+1)) 

where lookup value in e2 , number of cells include either side in d2. if d2 contains zero, value corresponding key (25). lookup , return columns don't need together.

the example includes cells in column b containing 9,16,25,36 , 49 , gives answer 27.

i should add error handling cases cells near end or beginning of range give right answer when time allows.

here formula error handling:-

=iferror(   if(or((match(e2,a2:a11,0)-d2)<1,(match(e2,a2:a11,0)+d2)>rows(b2:b11)),       "out of range",        average(offset(b1,match(e2,a2:a11,0)-d2,0,d2*2+1))),   "not found") 

here formula modified 'taper off' @ ends of range number of cells either side maximum of n, n number of cells between matching cell , end of range:-

=iferror(    average(offset(b$1,      match(e2,a$2:a$11,0)-min(match(e2,a$2:a$11,0)-1,rows(a$2:a$11)-match(e2,a$2:a$11,0),d$2),0,      min(match(e2,a$2:a$11,0)-1,rows(a$2:a$11)-match(e2,a$2:a$11,0),d$2)*2+1)),   "not found") 

---

update

index may preferred offset because not volatile.

here 2 formulae using index:-

=iferror(       if(or((match(e2,a2:a11,0)-d2)<1,(match(e2,a2:a11,0)+d2)>rows(b2:b11)),           "out of range",            average(index(b2:b11,match(e2,a2:a11,0)-d2):index(b2:b11,match(e2,a2:a11,0)+d2) )), "not found") 

and

=iferror(      average(          index(b$2:b$11,match(e2,a$2:a$11,0)-min(match(e2,a$2:a$11,0)-1,rows(a$2:a$11)-match(e2,a$2:a$11,0),d$2)):          index(b$2:b$11,match(e2,a$2:a$11,0)+min(match(e2,a$2:a$11,0)-1,rows(a$2:a$11)-match(e2,a$2:a$11,0),d$2))),    "not found")