Look up row from sheet1 and return average of cells from row.

[pgfindlay]

Hi,

I'm looking for help on a formula to look up a row based on a cell value and return the average of adjacent cells from that row.

eg: look up Duck and return the average of cells.

Duck	2	1	4	1	3	4
Chicken	4	5	3	6	3	4
Dog	2	4	3	1	3	5

<colgroup><col width="64" span="7" style="width:48pt"> </colgroup><tbody>
</tbody>

 

[Eric W]

Perhaps:

Excel 2012

	A	B	C	D	E	F	G
1	Duck	2	1	4	1	3	4
2	Chicken	4	5	3	6	3	4
3	Dog	2	4	3	1	3	5
4
5
6
7
8	Word	Average
9	Duck	2.5

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet3​

Worksheet Formulas Cell Formula B9 =AVERAGE(INDEX(B1:G3,MATCH(A9,A1:A3,0),0)) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

 

[FDibbins]

Try this...

	A​	B​	C​	D​	E​	F​	G​	H​	I​
1​	Duck	2​	1​	4​	1​	3​	4​	Chicken	4.166667​
2​	Chicken	4​	5​	3​	6​	3​	4​
3​	Dog	2​	4​	3​	1​	3​	5​

I1=AVERAGE(INDEX(A1:G3,MATCH(H1,A1:A3,0),))

 

[pgfindlay]

Awesome! Thanks Eric.

 

[Eric W]

Glad we could help.