# Averaging many Vlookups

#### Captain Smith

##### Active Member
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))

### Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.

#### Peter_SSs

##### MrExcel MVP, Moderator
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))

Replies
7
Views
128
Replies
2
Views
294
Replies
7
Views
224
Replies
6
Views
265
Replies
6
Views
120

1,191,671
Messages
5,987,958
Members
440,122
Latest member
branhill

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Which adblocker are you using?

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back