Averaging many Vlookups

[Captain Smith]

Is there a better approach than the following? Is there a way of using just one Vlookup reference instead of having to refer to eight different ones to achieve one average? Thanks.

=AVERAGE(VLOOKUP(A4,Sheet1!A20:GV20,11,FALSE),VLOOKUP(A4,Sheet1!A20:GV20,12,FALSE),VLOOKUP(A4,Sheet1!A20:GV20,13,FALSE),VLOOKUP(A4,Sheet1!A20:GV20,14,FALSE),VLOOKUP(A4,Sheet1!A20:GV20,15,FALSE),VLOOKUP(A4,Sheet1!A20:GV20,16,FALSE),VLOOKUP(A4,Sheet1!A20:GV20,17,FALSE),VLOOKUP(A4,Sheet1!A20:GV20,18,FALSE))

 

[Peter_SSs]

Your formula, as stated, seems unusual because your lookup range (A20:GV20) is only 1 row high.

If that is correct, then maybe this would do:
=IF(Sheet1!A20=A4,AVERAGE(Sheet1!K20:R20),"")

However, if the lookup range is, say, A20:GV30 then try this formula which must be confirmed with Ctrl+Shift+Enter, not just Enter:
=AVERAGE(VLOOKUP(A4,Sheet1!A20:GV30,{11,12,13,14,15,16,17,18},FALSE))