Average a range using vlookup as reference

[Alpacino]

I have a sheet with a alot of data in it. columns are in week numbers rows are staff names starts from row 25
Eg
Columns Col A Col B wk 1
Rows 25. Jeff. 5.6

Down column 1 is staff going down to about row 300

Its week 12 I would like to find out the average of jeff from week 1 to wk 12
I have a summary sheet on a different sheet is there a way to find this out.
PS This is a dynamic worksheet due to week data is added weekly.

Thanks in advance

 

[T. Valko]

I have a sheet with a alot of data in it. columns are in week numbers rows are staff names starts from row 25
Eg
Columns Col A Col B wk 1
Rows 25. Jeff. 5.6

Down column 1 is staff going down to about row 300

Its week 12 I would like to find out the average of jeff from week 1 to wk 12
I have a summary sheet on a different sheet is there a way to find this out.
PS This is a dynamic worksheet due to week data is added weekly.

Thanks in advance

Try this...

Book1
	A	B	C	D	E	F
1	_	1	2	3	4	5
2	Tom	27	72	21	18	46
3	Sue	72	40	96	23	87
4	Jeff	32	78	84	87	4
5	Lisa	80	34	85	76	40
6	_	_	_	_	_	_
7	Name	Week	AVG	_	_	_
8	Jeff	4	70.25	_	_	_
Sheet1

Formula entered in C8:

=AVERAGE(INDEX(B2:B5,MATCH(A8,A2:A5,0)):INDEX(B2:F5,MATCH(A8,A2:A5,0),B8))

 

[Alpacino]

I have tried it it doesn't work doesn't recognise :

 

[VoG]

Working here

Excel Workbook
	A	B	C	D	E	F
1	_	1	2	3	4	5
2	Tom	27	72	21	18	46
3	Sue	72	40	96	23	87
4	Jeff	32	78	84	87	4
5	Lisa	80	34	85	76	40
6	_	_	_	_	_	_
7	Name	Week	AVG	_	_	_
8	Jeff	4	70.25	_	_	_
Sheet1

 

[DonkeyOte]

I have tried it it doesn't work doesn't recognise :

Alpacino, where are you based ?

 

[Alpacino]

The wk number look up is coming from a different sheet so is the name Eg this table will be in input sheet and week number and name will be in summary sheet will that make a difference???

 

[Alpacino]

Alpacino, where are you based ?

What do you mean?? Sorry

 

[Alpacino]

Does the lookup Table have to be at the top of the worksheet??

 

[markmzz]

Does the lookup Table have to be at the top of the worksheet??

No. look at this example:

Note: I used the formula of T. Valko.

<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th><th>H</th><th>I</th><th>J</th><th>K</th><th>L</th><th>M</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="font-weight: bold;text-align: center;;">Name</td><td style="font-weight: bold;text-align: center;;">Weeks</td><td style="font-weight: bold;text-align: center;;">Avg</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: center;;">Name01</td><td style="text-align: center;;">9</td><td style="text-align: center;;">4.01</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: center;;">Name02</td><td style="text-align: center;;">3</td><td style="text-align: center;;">4.77</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: center;;">Name03</td><td style="text-align: center;;">12</td><td style="text-align: center;;">4.74</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: center;;">Name04</td><td style="text-align: center;;">7</td><td style="text-align: center;;">5.66</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="text-align: center;;">Name05</td><td style="text-align: center;;">10</td><td style="text-align: center;;">5.31</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="text-align: center;;">Name06</td><td style="text-align: center;;">4</td><td style="text-align: center;;">3.83</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style="text-align: center;;">Name07</td><td style="text-align: center;;">5</td><td style="text-align: center;;">3.62</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style="text-align: center;;">Name08</td><td style="text-align: center;;">3</td><td style="text-align: center;;">5.13</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">24</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">25</td><td style="font-weight: bold;;">Name/Week</td><td style="font-weight: bold;text-align: center;;">1</td><td style="font-weight: bold;text-align: center;;">2</td><td style="font-weight: bold;text-align: center;;">3</td><td style="font-weight: bold;text-align: center;;">4</td><td style="font-weight: bold;text-align: center;;">5</td><td style="font-weight: bold;text-align: center;;">6</td><td style="font-weight: bold;text-align: center;;">7</td><td style="font-weight: bold;text-align: center;;">8</td><td style="font-weight: bold;text-align: center;;">9</td><td style="font-weight: bold;text-align: center;;">10</td><td style="font-weight: bold;text-align: center;;">11</td><td style="font-weight: bold;text-align: center;;">12</td></tr><tr ><td style="color: #161120;text-align: center;">26</td><td style="font-weight: bold;text-align: center;;">Name07</td><td style="text-align: center;;">3.90</td><td style="text-align: center;;">5.60</td><td style="text-align: center;;">2.80</td><td style="text-align: center;;">2.60</td><td style="text-align: center;;">3.20</td><td style="text-align: center;;">2.80</td><td style="text-align: center;;">7.40</td><td style="text-align: center;;">3.00</td><td style="text-align: center;;">7.70</td><td style="text-align: center;;">2.20</td><td style="text-align: center;;">2.80</td><td style="text-align: center;;">6.80</td></tr><tr ><td style="color: #161120;text-align: center;">27</td><td style="font-weight: bold;text-align: center;;">Name02</td><td style="text-align: center;;">3.20</td><td style="text-align: center;;">4.30</td><td style="text-align: center;;">6.80</td><td style="text-align: center;;">5.20</td><td style="text-align: center;;">3.00</td><td style="text-align: center;;">5.30</td><td style="text-align: center;;">6.40</td><td style="text-align: center;;">5.30</td><td style="text-align: center;;">4.10</td><td style="text-align: center;;">3.40</td><td style="text-align: center;;">5.10</td><td style="text-align: center;;">4.10</td></tr><tr ><td style="color: #161120;text-align: center;">28</td><td style="font-weight: bold;text-align: center;;">Name06</td><td style="text-align: center;;">6.10</td><td style="text-align: center;;">2.00</td><td style="text-align: center;;">4.60</td><td style="text-align: center;;">2.60</td><td style="text-align: center;;">4.00</td><td style="text-align: center;;">7.90</td><td style="text-align: center;;">7.60</td><td style="text-align: center;;">2.10</td><td style="text-align: center;;">6.20</td><td style="text-align: center;;">6.90</td><td style="text-align: center;;">6.10</td><td style="text-align: center;;">2.90</td></tr><tr ><td style="color: #161120;text-align: center;">29</td><td style="font-weight: bold;text-align: center;;">Name01</td><td style="text-align: center;;">3.50</td><td style="text-align: center;;">3.80</td><td style="text-align: center;;">3.80</td><td style="text-align: center;;">3.10</td><td style="text-align: center;;">3.70</td><td style="text-align: center;;">2.70</td><td style="text-align: center;;">5.00</td><td style="text-align: center;;">7.30</td><td style="text-align: center;;">3.20</td><td style="text-align: center;;">7.90</td><td style="text-align: center;;">7.60</td><td style="text-align: center;;">4.30</td></tr><tr ><td style="color: #161120;text-align: center;">30</td><td style="font-weight: bold;text-align: center;;">Name05</td><td style="text-align: center;;">7.70</td><td style="text-align: center;;">5.60</td><td style="text-align: center;;">5.00</td><td style="text-align: center;;">4.80</td><td style="text-align: center;;">7.80</td><td style="text-align: center;;">4.70</td><td style="text-align: center;;">2.00</td><td style="text-align: center;;">6.40</td><td style="text-align: center;;">4.80</td><td style="text-align: center;;">4.30</td><td style="text-align: center;;">5.20</td><td style="text-align: center;;">5.20</td></tr><tr ><td style="color: #161120;text-align: center;">31</td><td style="font-weight: bold;text-align: center;;">Name03</td><td style="text-align: center;;">7.20</td><td style="text-align: center;;">6.10</td><td style="text-align: center;;">4.10</td><td style="text-align: center;;">2.50</td><td style="text-align: center;;">6.40</td><td style="text-align: center;;">8.00</td><td style="text-align: center;;">3.60</td><td style="text-align: center;;">4.70</td><td style="text-align: center;;">6.00</td><td style="text-align: center;;">2.70</td><td style="text-align: center;;">2.00</td><td style="text-align: center;;">3.60</td></tr><tr ><td style="color: #161120;text-align: center;">32</td><td style="font-weight: bold;text-align: center;;">Name04</td><td style="text-align: center;;">4.90</td><td style="text-align: center;;">3.80</td><td style="text-align: center;;">7.40</td><td style="text-align: center;;">6.40</td><td style="text-align: center;;">7.20</td><td style="text-align: center;;">4.40</td><td style="text-align: center;;">5.50</td><td style="text-align: center;;">2.70</td><td style="text-align: center;;">7.70</td><td style="text-align: center;;">4.60</td><td style="text-align: center;;">6.10</td><td style="text-align: center;;">3.60</td></tr><tr ><td style="color: #161120;text-align: center;">33</td><td style="font-weight: bold;text-align: center;;">Name08</td><td style="text-align: center;;">4.70</td><td style="text-align: center;;">5.90</td><td style="text-align: center;;">4.80</td><td style="text-align: center;;">6.30</td><td style="text-align: center;;">4.80</td><td style="text-align: center;;">5.80</td><td style="text-align: center;;">4.10</td><td style="text-align: center;;">3.40</td><td style="text-align: center;;">5.20</td><td style="text-align: center;;">7.70</td><td style="text-align: center;;">7.60</td><td style="text-align: center;;">6.20</td></tr></tbody></table><p style="width:3em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Plan2</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">C2</th><td style="text-align:left">=AVERAGE(<font color="Blue">INDEX(<font color="Red">$B$26:$M$33,MATCH(<font color="Green">A2,$A$26:$A$33,0</font>),1</font>):INDEX(<font color="Red">$B$26:$M$33,MATCH(<font color="Green">A2,$A$26:$A$33,0</font>),B2</font>)</font>)</td></tr></tbody></table></td></tr></table><br />

Markmzz

 

[T. Valko]

I have tried it it doesn't work doesn't recognise :

The wk number look up is coming from a different sheet so is the name Eg this table will be in input sheet and week number and name will be in summary sheet will that make a difference???

Here's a small sample file that demonstrates this.

zzzAlpacino.xls 16kb

http://cjoint.com/?AGrpjWDiqqZ